Querying SQL Server Agent Job History Data

By: Chad Churchwell   |   Updated: 2022-04-06   |   Comments (24)   |   Related: 1 | 2 | 3 | 4 | > SQL Server Agent

Problem

Often times we are tasked with having to programmatically come with a list of durations per SQL Server Agent Job to trend the run times and order the results by date. Unfortunately Microsoft has not made that very easy in the way the data is stored in the system tables in the MSDB database. In this tip I will explain how to use the system tables to get the data into the correct format for dates and job durations.

Solution

SQL Server stores job information in the MSDB system tables. The table that stores historical job executions is msdb.dbo.sysjobhistory and the date/time information as well as the duration of the job execution is stored a little different than what you will see in most system tables. Let's dive into the tables to learn how to perform the calculations.

MSDB.dbo.sysjobhistory Table - run_date and run_time Columns

In the msdb.dbo.sysjobhistory table, the date (run_date column) and time (run_time column) are stored as two different columns and the columns are an INT data type as seen in the screenshot below.

If you were to query the sysjobhistory (joined to sysjobs of course) this is what the raw data would look like. Note run_date appears to be in YYYYMMDD format but the time is a little trickier. It is in format HHMMSS and not always 6 numbers long as you can see in this example the times are 3:30 AM represented by 33000 in the run_time column.

The result set in the picture above was generated by running the following T-SQL script:

select 
 j.name as 'JobName',
 run_date,
 run_time
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, run_date, run_time desc

This can be very confusing when trying to analyze these results and more importantly trying to order results by date and time, which as a common practice for DBA's. There are many ways to get the date/time into a true DATETIME format and I have seen some very creative scripts over the years. Starting in SQL Server 2005 there is an system scalar function located in MSDB that will convert this for you. I say it is undocumented as I could not find a BOL article for it. The function name is MSDB.dbo.agent_datetime(run_date, run_time) and will return a nicely formatted DATETIME column that is much easier to use programmatically. Below is the same query as the first one above with just an additional column that is a call to this function.

select 
 j.name as 'JobName',
 run_date,
 run_time,
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, RunDateTime desc

Below are the results that this query produces and the nicely formatted DATETIME column at the end. This makes sorting easy as well as date range queries against your SQL Agent Jobs.

MSDB.dbo.sysjobhistory Table and run_duration Column

In the msdb.dbo.sysjobhistory table, the duration (run_duration column) is also stored as an INT and can be very confusing when querying. It is a duration stored in HHMMSS format similar to run_time except this is a duration. As an example 2300 would be 23 minutes. 15467 would be 1 hour, 54 minutes, and 67 seconds. Lets add the run_duration column to our query and see what the results look like.

select 
 j.name as 'JobName',
 run_date,
 run_time,
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 run_duration
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, RunDateTime desc

As you can see there are different length returns returned in the run_duration column. 2 digits would be just seconds, 3 digits would be single digit minutes and seconds. This can be very confusing when trying to produce a report on job duration over a time period.

Now we can add another column that will take this run_duration and convert it into something meaningful. In this example I chose minutes as I don't really have too much concern for jobs that run in seconds. Here is the an updated version of T-SQL script with this additional column added that will round to the nearest minute for the run_duration column.

select 
 j.name as 'JobName',
 run_date,
 run_time,
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 run_duration,
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
          as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, RunDateTime desc

You can see that the columns that have an original run_duration that are 2 digits are either 0 or 1 depending if they are over or under 30 seconds. Also you can see the original run_duration values that are 3 digits are rounded to the nearest minute.

T-SQL Script for SQL Server Agent Job History

Up to this point I have shown how to handle the date/time columns to get into a DATETIME formatted single column as well as the job durations into minutes. This makes the analysis and auditing of your SQL Server Agent Jobs much easier. Below is the final script with the original unformatted columns removed. This will give you a list of jobs ordered by name and run date with their associated duration.

select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

T-SQL Script for SQL Server Agent Job Step History

I modified the script above and joined the msdb.dbo.sysjobsteps table in order to get the duration by job step. This information is helpful for long ETL jobs that have many steps in order to determine which step is taking the longest to run within the job.

select 
 j.name as 'JobName',
 s.step_id as 'Step',
 s.step_name as 'StepName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobsteps s 
 ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h 
 ON s.job_id = h.job_id 
 AND s.step_id = h.step_id 
 AND h.step_id <> 0
where j.enabled = 1   --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '12/08/2012' and '12/10/2012'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

Next Steps

• Test the scripts in your environment to provide insight for auditing in your environment or to produce a report when durations tend to deviate from the baseline.
• These scripts should work for SQL Server 2005 through SQL Server 2019
• Check out these additional resources:
  • SQL Server Agent Tips
  • Analyzing SQL Agent Job and Job Step History in SQL Server
  • SQL Server user defined function to convert MSDB integer value to time value
  • Accessing SQL Server Agent Data

About the author

Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

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

View all my tips