Problem
We are tasked with building a list of SQL Server Agent Jobs to trend run times by date. This is not easy based on the way data is stored in the system tables in the MSDB database. In this tip, I will explain how to check job history in SQL Server using queries against the system tables. These include sysjobs and sysjobhistory 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. The date/time information and job execution duration are stored differently than 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 descThis 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 descBelow 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 descAs 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 descYou 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 descT-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 descNext 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 2022
- Check out these additional resources:
