By Richie Lee

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