SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The query below returns both the duration and the start time of a job within the past 24 hours. This is useful in determining any long running jobs over the past 24 hours, which helped me when I was trying to locate a job that was generating a lot of transactions and causing the log backups to grow quite large. The reason this was a problem for me was that I had a log shipping database that was used for reporting on, and the creation of the transaction undo file (tuf file) in between each backup with STANDBY was causing the restore job to take a lot longer than normal. I’ll pick up on how to fix this issue in a later post, as the reporting database needed to be back up and online with as little down time as possible.

,db_run = rs.db
,run_duration = CONVERT(CHAR(20), DATEADD(second, rd, 0), 108)
,run_datetime = CONVERT (CHAR (50), rundatetime, 108)
,rd = DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_duration), 6), 5, 0, ':'), 3, 0, ':'))
,rundatetime = STUFF (STUFF (CONVERT(VARCHAR (20), h.run_date), 5, 0, '-'), 8, 0, '-') + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_time), 6), 5, 0, ':'), 3, 0, ':')
,db = s.database_name
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE h.step_id = 0
and run_duration >= -1
GROUP BY j.NAME,s.database_name, s.step_name, run_duration, run_date, run_time
AS rs
WHERE CONVERT (CHAR (50), rundatetime, 108) > DATEADD (HOUR, -24, GETDATE())
ORDER BY run_datetime desc