This script will get the job history for a particular job. The start time will be converted from integer to datetime. Works for job duration under 1000 hours.
Since I’ve still got clients on SQL 2008, I can’t use the DATETIMEFROMPARTS function, which makes the code a little longer than on newer SQL versions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
USE msdb go DECLARE @job_name SYSNAME = 'job name', @start_time DATETIME = '2018-10-01 00:00:00'; WITH cte_jobhistory AS (--pad duration with zeros) SELECT sjh.step_id, sjh.step_name, run_status, run_date, run_time, run_duration, dbo.Agent_datetime(run_date, run_time) AS start_time, RIGHT('0000000' + Cast(run_duration AS VARCHAR(7)), 7) AS duration_padded FROM dbo.sysjobhistory sjh INNER JOIN dbo.sysjobs sj ON sj.job_id = sjh.job_id LEFT OUTER JOIN dbo.sysjobsteps sjs ON sjh.job_id = sjs.job_id AND sjh.step_id = sjs.step_id WHERE sj.NAME = @job_name), cte_duration AS (--separate hours, minutes & seconds) SELECT *, Cast(Substring(duration_padded, 1, 3) AS INT) AS duration_hours, Cast(Substring(duration_padded, 4, 2) AS INT) AS duration_minutes , Cast(Substring(duration_padded, 6, 2) AS INT) AS duration_seconds FROM cte_jobhistory) SELECT step_id, step_name, CASE run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In Progress' END AS run_status, start_time, RIGHT(duration_padded, 7) AS run_duration, Dateadd(hh, duration_hours, Dateadd(mi, duration_minutes, ( Dateadd(ss, duration_seconds, start_time) ))) AS end_time FROM cte_duration WHERE start_time > @start_time ORDER BY start_time, run_time, step_id; |