Querying SQL Server Agent Job History Data

By:   |   Updated: 2022-04-06   |   Comments (23)   |   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.

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Chad Churchwell 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


Article Last Updated: 2022-04-06

Comments For This Article




Monday, September 19, 2022 - 4:44:18 AM - Cynthia Back To Top (90493)
very helpful, thanks.
I am also looking for a way to see who keeps clearing my scheduled jobs history, on one of my SQL systems- any script to help here will be great. thanks

Tuesday, April 21, 2020 - 1:52:47 PM - Aidan Back To Top (85433)

Hi

You might want to consider using this for the duration: CAST(MSDB.dbo.agent_datetime(run_date, run_duration) AS TIME)

Aidan


Thursday, March 26, 2020 - 3:34:31 AM - Dev Back To Top (85194)

Thank you for MSDB.dbo.agent_datetime(run_date, run_time)


Monday, February 17, 2020 - 4:20:59 AM - Surendra Back To Top (84567)

Thanks it helped and saved by lot of time.


Thursday, January 24, 2019 - 11:07:59 AM - Allen Back To Top (78867)

 Hi,

One question, why the result retuned two rows for one job.


Sunday, September 23, 2018 - 10:21:38 AM - ABHIJEET SRIVASTAVA Back To Top (77707)

 Awesome stuff... Saved me days of coding :) 

 


Thursday, March 15, 2018 - 7:27:55 AM - Pradeesh Back To Top (75423)

Hi Chad :

     Your post really helped my requirement. In addition to that, i have a requirement to fetch the history of a particular job, like how we get thru Sql Server Agent, It will list all the job execution, under that  it will list the steps.  Like wise how to get the same using query. Below query helped to view the history of a job, but how to link the each steps under the appropriate parent job. 

 

SELECT 
 sh.*
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id and sh.job_id = '#########'
order by sh.run_date desc


Friday, December 22, 2017 - 1:48:08 AM - Arun Back To Top (74319)

 

 Thanks !!!

 

It worked for me...:)


Sunday, October 8, 2017 - 8:57:25 PM - Sean Back To Top (67061)

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 3, 2016 - 6:03:14 AM - Steph Back To Top (41605)

 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 (41540)

 This was a great help - thanks Chad.

 


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

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 (35938)

 

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 (35318)

Excellent!


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

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 (29247)

excellent solution for

Querying SQL Server Agent Job History Data

Karl


Monday, June 3, 2013 - 6:29:05 PM - Mike Back To Top (25267)

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 2, 2013 - 10:55:07 AM - Gaby A. Back To Top (21234)

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 (21121)

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 (21115)

 

Very nice find.


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

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 (20962)

 

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 (20959)

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















get free sql tips
agree to terms