Determining the Status of a Scheduled Job

When scheduling jobs in a database, it's essential to be able to monitor their status for various purposes. This article addresses three key questions related to job status:

1. Viewing Scheduled Jobs

To view a list of all jobs scheduled for future execution, use the following query:

SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON
    job.job_id = activity.job_id
WHERE
    activity.run_requested_date > GETDATE();

2. Monitoring Running Jobs

To view the list of currently running jobs, execute the following query:

SELECT JOB_ID,
       NAME,
       START_TIME,
       TIME_RUNNING,
       [STATUS],
       AGENT_NAME
FROM
    [MSDB].[dbo].[sysjobs]
WHERE
    [STATUS] = 2
    AND TIME_RUNNING > 0;

3. Assessing Job Completion Status

To determine whether a job has completed successfully or encountered an error, use this query:

SELECT
    RUN_REQUESTED_DATE,
    RUN_START_DATE,
    RUN_COMPLETION_DATE,
    ERROR_MESSAGE
FROM
    [MSDB].[dbo].[sysjobhistory]
ORDER BY
    RUN_REQUESTED_DATE DESC;

The RUN_COMPLETION_DATE field will indicate the job's completion time, while the ERROR_MESSAGE field will provide any error messages encountered during execution.