How to Determine the Status of a Job

How can I determine the status of a job?

You could try using the system stored procedure sp_help_job. This returns information on the job, its steps, schedules and servers. For example

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

SQL Books Online should contain lots of information about the records it returns.

For returning information on multiple jobs, you could try querying the following system tables which hold the various bits of information on the job

• msdb.dbo.SysJobs
• msdb.dbo.SysJobSteps
• msdb.dbo.SysJobSchedules
• msdb.dbo.SysJobServers
• msdb.dbo.SysJobHistory

Their names are fairly self-explanatory (apart from SysJobServers which hold information on when the job last run and the outcome).

Again, information on the fields can be found at MSDN. For example, check out the page for SysJobs

how to know status of currently running jobs

It looks like you can use msdb.dbo.sysjobactivity, checking for a record with a non-null start_execution_date and a null stop_execution_date, meaning the job was started, but has not yet completed.

This would give you currently running jobs:

SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL

Check Status of SQL Server Job

This code is what I needed. Taken from MSDN

Thanks @JeroenMostert

SELECT sj.Name, 
    CASE
        WHEN sja.start_execution_date IS NULL THEN 'Not running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
    END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity); 

How to check status for jobs in list each n seconds till all jobs finished?

Response syntax of get_transcription_job method is:

{
    'TranscriptionJob': {
        'TranscriptionJobName': 'string',
        'TranscriptionJobStatus': 'QUEUED'|'IN_PROGRESS'|'FAILED'|'COMPLETED',
        ...
    }
}

So instead of adding 'status' object which contains other fields, you need to append status.get('TranscriptionJob').get('TranscriptionJobStatus')

Change:

final_list.append(status)

By:

final_list.append(status.get('TranscriptionJob').get('TranscriptionJobStatus'))

UPDATE:

According to what are you trying to do, the condition should be:

if all(status in ['COMPLETED', 'FAILED'] for status in final_list):

Because you need to validate that allowed values list ('COMPLETED', 'FAILED') contains all the elements of 'final_list'.

---

Reference:

Boto3 get_transcription_job

How to get status of job using Scheduler?

The job itself has no status message like ("Success", "Failed") because the job does not know if the logic in the scheduled function was "successful".
The user must therefore program the logic himself whether the execution has proceeded as desired.
If you want to know if the job was executed at all, regardless of the result, you can look at the number of executions.
The job has the properties attempts, max_attempts and has_attempts_remaining for this purpose. Here you can find the doc page.
Maybe this example can help you too.

Disclosure: I am one of the authors of the library.

How do I check job status from SSIS control flow?

You may want to create a third package the runs packageA and then packageB. The third package would only contain two execute package tasks.

http://msdn.microsoft.com/en-us/library/ms137609.aspx

@Craig
A status table is an option but you will have to keep monitoring it.

Here is an article about events in SSIS for you original question.

http://www.databasejournal.com/features/mssql/article.php/3558006

---

Related Topics