Query SQL Server Job Agent Execution Information and History
There are multiple ways to access SQL Job Agent jobs execution statistics, such as:
- Through SQL Server Management Objects (SMO): which provide a programmatic way of accessing and controlling many SQL Server objects. The Smo.Agent namespace will provide the required objects for accessing job statistics such as the last time each job (or step) ran, the duration the job ran for and the returned status as well as many other relevant information regarding job execution.
- Through undocumented Stored Procedures such as master.dbo.xp_sqlagent_enum_jobs: Although this doesn’t provide a lot of flexibility, particularly if you are executing and consuming the data within the context of T-SQL, rather than programmatically through wrapper classes that simplifies handling stored procedures with multiple output tables. It is important to note that since this stored procedure is undocumented, Microsoft could potentially retire it without prior notice, leaving your code non-functional.
- Through Job Agent System Tables: In my opinion this provides the best and most supported method of access. for the rest of this post I will be discussing the different System Tables provided to access SQL Server Job Agent statistics and execution information.
Job Agent Run Statistics through the msdb.dbo.sysjobhistory System Table
This provides the most comprehensive source of historical statistics about the execution of Job Agent jobs, the amount of records kept in this System Table is based on the Job Agent logging policy that can be setup by right-clicking on the Job Agent in SSMS (or SQL Data Tools) and selecting Properties.
One thing to note here is that the msdb.dbo.sysjobhistory System Table provides job run times and duration using an Integer column of the format yyMMdd for run_date, and mmhhss for both the run_time and duration columns, so it can be a bit “fiddly” to convert those into a simple DateTime column for when the run started and when it ended, hence I created this handy little script that essentially takes care of decoding temporal data into a format easily readable, and can be quickly plugged into an SSRS report.
name,
CONVERT(DATETIME,CONVERT(CHAR(8),run_date) + ' ' + STUFF(STUFF((LEFT('000000',6-LEN(run_time))+ CONVERT(VARCHAR(6),run_time)),3,0,':'),6,0,':')) AS start_time,
DATEADD(MINUTE,DATEDIFF(MINUTE, '0:00:00', CONVERT(TIME,STUFF(STUFF((LEFT('000000',6-LEN(run_duration))+ CONVERT(VARCHAR(6),run_duration)),3,0,':'),6,0,':'))),CONVERT(DATETIME,CONVERT(CHAR(8),run_date) + ' ' + STUFF(STUFF((LEFT('000000',6-LEN(run_time))+ CONVERT(VARCHAR(6),run_time)),3,0,':'),6,0,':'))) AS end_time,
run_status,
instance_id
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE step_id = 0
ORDER BY 2
A few things to keep in mind here:
- This gives execution run time statistics for jobs rather than individual steps, hence we filter by step_id = 0 which is actually the (outcome) step that every job has, which summarizes the execution of the Job Agent job including the total time it took to run the job in total and run_status of the job itself (rather than individual steps).
- In order to get around the date and time formatting I used a combination of LEFT and STUFF functions to convert the Integer into a uniform string of the format yyyyMMdd hh:mm:ss, which I can then convert into DateTime.
Now just for the sake of completeness, I will provide the other two System Tables that can give you run time and statistics information of the last time the job ran. Although 90% of the time this might not be so useful, since the System Table above will cover that information and more, but in case you have a job that has not run for a long time and the SQL Server Job Agent log maintenance operation has cleared all run statistics pertenant to that job from the msdb.dbo.sysjobhistory System Table, then you could use these two other System Tables to interrogate the run statistics of the job as a whole, or for each individual step in that job.
Job Agent Run Statistics through the msdb.dbo.sysjobsteps System Table
This System Table will contain last execution (or run) statistics regarding each step in a Job Agent job, as well as many other information related to how the step is setup and configured.
To access the run date and time statistics for the job in total through the msdb.dbo.sysjobsteps System Table, I have created the following procedure.
(
SELECT
job_id,
step_id,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY step_id ASC) AS row_num
FROM msdb.dbo.sysjobsteps js
GROUP BY job_id,step_id
)
SELECT
name,
CONVERT(DATETIME,CONVERT(CHAR(8),MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_date ELSE NULL END)) + ' ' + STUFF(STUFF((LEFT('000000',6-LEN(MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_time ELSE NULL END)))+ CONVERT(VARCHAR(6),MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_time ELSE NULL END))),3,0,':'),6,0,':')) AS start_time,
DATEADD(MINUTE,SUM(DATEDIFF(MINUTE, '0:00:00', CONVERT(TIME,STUFF(STUFF((LEFT('000000',6-LEN(last_run_duration))+ CONVERT(VARCHAR(6),last_run_duration)),3,0,':'),6,0,':')))),CONVERT(DATETIME,CONVERT(CHAR(8),MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_date ELSE NULL END)) + ' ' + STUFF(STUFF((LEFT('000000',6-LEN(MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_time ELSE NULL END)))+ CONVERT(VARCHAR(6),MAX(CASE WHEN ts.step_id = js.step_id THEN last_run_time ELSE NULL END))),3,0,':'),6,0,':'))) AS end_time,
CASE WHEN MIN(last_run_outcome) = 0 THEN 0 ELSE 1 END AS run_status
FROM
msdb.dbo.sysjobsteps js
INNER JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
LEFT JOIN top_step ts ON js.step_id = ts.step_id AND js.job_id = ts.job_id AND ts.row_num = 1
GROUP BY name
ORDER BY
2
It is a bit complex, since its related to each step in the job rather than the job in total, but in reality this is just to show off how to convert the time format in that System Table to something more user friendly (from Integer to DateTime).
Job Agent Run Statistics through the msdb.dbo.sysjobactivity System Table
This is the easiest way to access last run execution statistics for a job, since it provide run time statistics in DateTime format rather than Integer. Again I have created the stored procedure below to demonstrate how to access the information in msdb.dbo.sysjobactivity.
name,
start_execution_date AS start_time,
stop_execution_date AS end_time,
ISNULL(last_run_outcome,1) AS run_status
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
LEFT JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id AND ja.last_executed_step_id = js.step_id
WHERE
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NOT NULL
And that is it, now you can access historical performance information regarding each step and job in SQL Server Job Agent, as well as run statistics regarding steps or jobs that haven’t executed for a while. With this information you can create timelines to facilitate visualizing how jobs are scheduled on a particular server, and if there are any conflict to avoid, or windows of inactivity that jobs should be rescheduled into.
Query SQL Server Job Agent Execution Information and Historyrated 4 out of 5 by 1 readers
Trackbacks & Pingbacks
[…] SSRS report uses Job Agent System Tables to grab historical job run information, the query for which is embedded within the report itself (so no need to create a stored procedure […]
Leave a Reply
Want to join the discussion?Feel free to contribute!