Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Querying SQL Server Agent Job History Data


By:   |   Read Comments (15)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Agent

Attend these FREE MSSQLTips webcasts >> click to register


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.

msdb.dbo.sysjobhistory table

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.

Original Columns with DATE/TIME from the MSDB.dbo.sysjobhistory table

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.

New Columns with DATE/TIME formatted data from the msdb.dbo.sysjobhistory table

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.

Original Columns for Duration from the msdb.dbo.sysjobhistory table

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.

Original Columns for Duration and Run Duration in Minutes from the msdb.dbo.sysjobhistory table

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


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, October 08, 2017 - 8:57:25 PM - Sean Back To Top

Your conversion doesn't work if hours > 24.

The run_duration value 365446 becomes 05:30:46:000, but really that's 36h:54m:46s. View History in SSMS reports this value as 1.12:54:46

 


Friday, June 03, 2016 - 6:03:14 AM - Steph Back To Top

 Hello

Very nice indeed, however the last script " T-SQL Script for SQL Server Agent Job Step History" does not display the real duration of each step

Would have been great to get it in seconds versus in Minutes  because this precision is making a huge difference when you try to debug step duration.

I made the test on one of my job , it ran in 1 min and 26 sec

but the script " T-SQL Script for SQL Server Agent Job Step History" only display 1 min !!   means here we are missing the 26 Sec remaining.

 

 

 

 


Monday, May 23, 2016 - 2:54:03 PM - Seth Delconte Back To Top

 This was a great help - thanks Chad.

 


Wednesday, November 11, 2015 - 11:33:15 AM - Susie Kenowski Back To Top

excellent article thanks so much,

I also need to know the name of the SSIS Package the Agent is running, is there a way to add this to your script above which lists the job steps?

 

thanks in Advance!


Wednesday, January 14, 2015 - 10:48:36 AM - Sean Perkins Back To Top

 

First off, this article was a huge help for a task that was assigned to me, thank you so much for the effort!  Another thing that'd be great to see is average run times.  How do you go about adding code to show the average run time of a job over a period of time? For example, a 3 day period, a week, 10 days, a month, etc.


Monday, November 17, 2014 - 11:39:07 PM - Ramya Back To Top

Excellent!


Tuesday, August 19, 2014 - 12:17:08 PM - Henry Stinson Back To Top

How could I include what stored procedures are called in each step?


Monday, January 27, 2014 - 8:28:16 PM - karl oliver Back To Top

excellent solution for

Querying SQL Server Agent Job History Data

Karl


Monday, June 03, 2013 - 6:29:05 PM - Mike Back To Top

One thing to note is that version 2008 and before had Run_Status 4 = In Progress.  It's no longer there in 2012.


Wednesday, January 02, 2013 - 10:55:07 AM - Gaby A. Back To Top

Great tip. For those who want to run this in SQL 2000, I ran sp_helptext agent_datetime and created the function in msdb.  Works fine.


Monday, December 24, 2012 - 10:40:57 AM - Ed - sqlscripter Back To Top

Good article, I have a stored procedure that figures the avg anf max run durations for each job which comes in quite handy. This one shows a unique column, the version column and every time you change even a comment in a job this is incremented 1 number. Also is the date modified. I was very surprised to see how many times the developers have touched the jobs. I left the create proc and header so you can see how long ago I wrote this.

 

CREATE Procedure [dbo].[sp_dba_getJobOwnerNames]
AS
/**************************************************************************
Author: Edward J Pochinski III 05/07/2002 
Revision 1.0
Usage: Get Job name,description,date created,modified,version and if logged
to NT event log
***************************************************************************/
select [name],
 [Description],
 SUSER_SNAME(OWNER_SID) AS Owner_name,
 LEFT([date_created],11)AS Date_Created,
 LEFT([date_Modified],11)AS Date_Modified,
 version_number,
 [Notify_level_EventLog]=
 CASE
  WHEN notify_level_eventlog = 0 THEN 'No Log'
  WHEN notify_level_eventlog = 1 THEN 'When Job Succeeds'
  WHEN notify_level_eventlog = 2 THEN 'When Job Fails'
  WHEN notify_level_eventlog = 3 THEN 'On Completion regardless'
 END

from MSDB.dbo.sysjobs
ORDER BY [Owner_Name]


Monday, December 24, 2012 - 7:52:49 AM - Chuck Back To Top

 

Very nice find.


Tuesday, December 18, 2012 - 8:20:47 AM - webtomte Back To Top

Some more useful info while troubleshooting like qho owns the job

select  
 j.name as 'JobName'
 ,SUSER_SNAME(j.owner_sid) as 'Jobowner'
 ,h.step_id
 ,h.step_name
 ,msdb.dbo.agent_datetime(h.run_date, h.run_time) as 'RunDateTime'
 ,h.run_status
 ,h.run_duration
 ,h.message
     --,*
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
 ,h.step_id desc


Friday, December 14, 2012 - 4:34:30 PM - John Back To Top

 

Hi folks, just wish to thank the ppl that contribute to make mssqltips

Such a wealth of info. Only just recently created a script for auditing Agent job details.

The recent posting by Chad making ref to the scalar function is excellent.

Keep up the gr8 work.

Rgds, john


Friday, December 14, 2012 - 10:29:43 AM - James Lawrence Back To Top

Sweet! The job history time thing has always been a pain...until now! Thanks very much for this article.


Learn more about SQL Server tools