Analyzing SQL Agent Job and Job Step History in SQL Server

By: Atif Shehzad   |   Comments (23)   |   Related: 1 | 2 | 3 | > SQL Server Agent

Problem

Almost every DBA configures SQL Agent Jobs for automation of repetitive tasks. This valuable facility works in the background in SQL Server and saves a lot of effort and manual rework. You may have a number of jobs scheduled and often there is the need to analyze the data to see which jobs are taking a long time or which job steps are taking a long time.  As you add more jobs and overhead to the server these times become even more critical and analyzing the data is key.  SSMS offers some insight into your jobs, but there is no easy way to slice and dice the data to see what is going on for a particular job or at a particular time of the day.

Some of the questions you may want to ask include:

• How much difference in performance is there for the current scheduled time compared to changing the time to midnight?
• Exactly how much time is consumed now to complete the job?
• Have jobs performed better after changes or there is some negative impact?
• Which step of a specified job is taking longer than it takes normally to complete?

Questions like these require analysis performed over the history of the jobs. So how do you generate the required set of data in a form that is suitable for analysis?  Or a situation where someone does not have access to SSMS or EM to access the job history for verification and analysis?

Solution

To generate a comprehensive and descriptive set from the job history the following script can be run in SSMS/QA. It uses the system database msdb and some of the system tables related to jobs and job history.

--Script # 1: To generate steps history of all jobs

USE msdb
GO

SELECT j.name JobName,h.step_name StepName, 
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, 
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, 
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded' 
when 2 then 'Retry' 
when 3 then 'Cancelled' 
when 4 then 'In Progress' 
end as ExecutionStatus, 
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name, h.run_date, h.run_time
GO

This script will return execution history of all jobs arranged by job, run date and run time. For each step in a job, related information is displayed to give you additional insight. We can analyze each job step and the execution time for each step. We can then see if there is an unusual duration of time consumed by this step versus other run times.

The Duration is presented in seconds. If the value is less than one second then zero is used for the duration.

For this example no filter has been applied, history of both enabled and disabled jobs is generated.

Two sets of job records have been highlighted above. Step [Job outcome] is associated with each set of steps in a job and its duration is shown as cumulative duration of all steps. Results of executing script # 1 will be different on your server according to your scheduled jobs.

Columns selected in script # 1 have the following descriptions.

By default SQL Server stores a maximum of 100 rows for a job. It is important to clarify that 100 rows are for the whole job not for a single step of a job. If you require more than 100 rows for analysis then you can change the default setting in the following way. Go to the Properties tab of SQL Server Agent in SSMS. Then you can configure the number of rows as shown in the screenshot below. After the change you will be able to store more historical rows for analysis as specified.

Next Steps

• Next time when you need to implement changes for your jobs do not forget to consider the impacts on your job schedule
• If you are required to maintain long term history data, then use the above mentioned method to increase the storage of history rows or you can create a separate archive table to keep this data to analyze
• To be more specific for a requirement, you can apply filters in the script on the sysJobHistory table. This table contains information about the execution of scheduled jobs by SQL Server Agent.
• You can also apply a filter on run_date, run _time, name or if you want apply a filter on run_status of the jobs and use the following mapping
  • 0 - Failed
  • 1 - Succeeded
  • 2 - Retry
  • 3 - Canceled
  • 4 - In progress

About the author

Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips