Get Run Duration of a SQL Agent Job

The MSDB system database holds all the information for any SQL Agent jobs set up on the server. But it is not straightforward to query the system tables to get information like the duration of jobs out of the tables in the correct format. With that in mind, here’s a little script that will return the total duration of the SQL Agent jobs. There are a couple of “WHERE” statements you can include or exclude if you want to include a specific job, or if you want to include all the steps of the jobs.

SELECT job_name AS JobName
	,run_datetime AS DateOfRun
	,run_duration AS RunDuration
FROM (
	SELECT job_name
		,run_datetime
		,SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' + SUBSTRING(run_duration, 5, 2) AS run_duration
	FROM (
		SELECT DISTINCT j.NAME AS job_name
			,run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
			,run_duration = RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6)
		FROM msdb..sysjobhistory h
		INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
		WHERE step_id = 0-- comment this out to get break down of each step
		) t
	) t
--where job_name = 'add job name here'
ORDER BY job_name
	,run_datetime DESC

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s