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