Checking the status of the Sql Server Agent Jobs

One of the checks I like to do at the start of each day is to look at the status of all SQL agent jobs that ran overnight. Job Activity Monitor in Enterprise Manager is very useful for this, however it can be time consuming to use this, especially if you have a lot of servers. In this article I've outlined some scripts that I run against each production server in order to perform these checks.
Just in case you are not aware, Job Activity Monitor is available in SQL Server Management Studio under the 'SQL Server Agent' items in Object
Explorer :

Double clicking on this will open up a new window containing details of all agent jobs

Getting a Complete Job History

Now, onto the scripts. The following query lists all jobs run in the last 24 hours, along with the time run, the
outcome, and whether the job is enabled :

USE MSDB
SELECT name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
,CASE WHEN SJH.run_status=0 THEN 'Failed'
WHEN SJH.run_status=1 THEN 'Succeeded'
WHEN SJH.run_status=2 THEN 'Retry'
WHEN SJH.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END [Job Outcome]
FROM sysjobhistory SJH
JOIN sysjobs SJ
ON SJH.job_id=sj.job_id
WHERE step_id=0
AND DATEADD(S,
(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())
ORDER BY name,run_date,run_time

This uses the sysjobhistory and sysjobs system tables in the msdb database. One curious aspect of these tables is that they store dates and times
as a numerical respresentation of a time and date such as 20120417. To make this more readable I've converted this into a more
conventional format.

To obtain status information about each job you can run the following stored procedure, (this is actually what Job Activity Monitor uses) :

exec msdb.dbo.sp_help_job

Listing “Problem” Jobs
One disadvantage of the queries above is that they can generate a lot of rows, especially if you have jobs that run frequently.
The following query is adapted from the one above, but has a maximum of one row per job.
It generates a list of jobs that failed the most recent time that they were run (if the job failed and then succeeded on a subsequent run then it won’t be listed).
This is useful for giving a "heads up" for potential problems to be investigated :

WITH CTE_MostRecentJobRun AS
(
-- For each job get the most recent run (this will be the one where Rnk=1)
SELECT job_id,run_status,run_date,run_time
,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk
FROM sysjobhistory
WHERE step_id=0
)
SELECT
name AS [Job Name]
,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]
,CASE WHEN enabled=1 THEN 'Enabled'
ELSE 'Disabled'
END [Job Status]
FROM CTE_MostRecentJobRun MRJR
JOIN sysjobs SJ
ON MRJR.job_id=sj.job_id
WHERE Rnk=1
AND run_status=0 -- i.e. failed
ORDER BY name

Listing Running Jobs Finally it’s useful to check on jobs that are still running, especially if they have been running for a long time.
The following query does just that :

exec msdb.dbo.sp_help_job @execution_status=1

As an alternative you can also use the undocumented procedure xp_sqlagent_enum_jobs to do this.
Note that you might need to use sp_configure to switch on Agent XPs in order to run this.
The following query also returns information about running queries.
As always with using undocumented stored procedures it should be used appropriately !

IF OBJECT_ID('tempdb.dbo.#RunningJobs') IS NOT NULL
DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs (
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT,
Current_Step INT,
Current_Retry_Attempt INT,
State INT )
INSERT INTO #RunningJobs EXEC master.dbo.xp_sqlagent_enum_jobs 1,garbage
SELECT
name AS [Job Name]
,CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE
CONVERT(VARCHAR,DATEADD(S,(next_run_time/10000)*60*60 /* hours */
+((next_run_time - (next_run_time/10000) * 10000)/100) * 60 /* mins */
+ (next_run_time - (next_run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(next_run_date),112)),100) END AS [Start Time]
FROM #RunningJobs JSR
JOIN msdb.dbo.sysjobs
ON JSR.Job_ID=sysjobs.job_id
WHERE Running=1 -- i.e. still running
ORDER BY name,next_run_date,next_run_time