Ever tried to figure out how long a step for a particular SQL Server job takes? You can of course get it from the Management Studio, but we’re database people! We want to see the trend over time not just a point in time! We want to write queries! Hooray for data!
You do a little digging, and find out the information you need is stored in the MSDB sysjobhistory. And look! There’s even a run_duration column! How easy is this! Uh, wait a second… That duration looks a little funny. It turns out run_duration data is being stored in a HHMMSS format. Why? I don’t know but it sure makes it hard to use.
Well, lucky for you I smashed through, hundreds, no thousands, perhaps millions of newsgroup articles to bring you the cream of the crop, the best of the best, step history query. This is the official Red Ryder, carbine action, two-hundred shot range model air rifle of job step history queries!
SELECT j.name JobName
, h.step_name StepName
, CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6),
5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDatetime
, DATEADD(SECOND,
( ( h.run_duration / 1000000 ) * 86400 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 1000000 )
* 1000000 ) ) / 10000 ) * 3600 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
/ 100 ) * 60 ) + ( h.run_duration - ( h.run_duration
/ 100 ) * 100 ),
CAST(STR(h.run_date, 8, 0) AS DATETIME)
+ CAST(STUFF(STUFF(RIGHT('000000'
+ CAST (h.run_time AS VARCHAR(6)), 6),
5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDatetime
, STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6,
0, ':') AS run_duration_formatted
, ( ( h.run_duration / 1000000 ) * 86400 ) + ( ( ( h.run_duration
- ( ( h.run_duration
/ 1000000 )
* 1000000 ) )
/ 10000 ) * 3600 )
+ ( ( ( h.run_duration - ( ( h.run_duration / 10000 ) * 10000 ) )
/ 100 ) * 60 ) + ( h.run_duration - ( h.run_duration / 100 )
* 100 ) AS RunDurationInSeconds
, CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus
, h.message MessageGenerated
FROM sysjobhistory h
INNER JOIN sysjobs j
ON j.job_id = h.job_id
ORDER BY h.run_date DESC
, h.run_time desc