Using SQL to Query up Your Agent Job Run History

It is important to keep an eye on the run history of your agent jobs. That way you are aware if your jobs fail or the time they take to run is starting to creep upward. Ultimately, you should at the very least have email notifications sent to tell you if your jobs fail. However, what if a job that used to take one hour to run is now taking 10 hours? How will you know this is happening if the job doesn’t actually fail? The only way is to make sure you are keeping an eye on it, but that can be cumbersome with SQL Server Management Studio. One beef I have with SSMS is that it tells you the time your job started and how long it took, but to get the end time you have to do the math. Ugh! The below SQL will give you the run history of your jobs with a start and end time! Woo hoo! No more date/time math. It will also give you the duration hours, minutes, and seconds in separate columns so you can easily see trends in the time your jobs take to run. I hope you find it helpful.

use msdb

select
	J.name,
	dbo.agent_datetime(H.run_date, H.run_time) start_time,
	DATEADD(second, run_duration%100, DATEADD(minute, run_duration/100%100, DATEADD(hour, run_duration/10000, dbo.agent_datetime(H.run_date, H.run_time)))) end_time,
	run_duration/10000 duration_hours,
	run_duration/100%100 duration_minutes,
	run_duration%100 duration_seconds,
	case
		when H.run_status = 0 then 'Failed'
		when H.run_status = 1 then 'Succeeded'
		when H.run_status = 2 then 'Retry'
		when H.run_status = 3 then 'Canceled'
		when H.run_status = 4 then 'In Progress'
	end run_status,
	H.message
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobhistory H on J.job_id = H.job_id
where step_id = 0
order by
	J.name, start_time desc