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
All the above queries have been tested on SQL Server 2005, 2008, 2008 R2 and 2012.