How long did a job run??
It should be simple:
select run_duration from msdb.dbo.sysjobhistory
BUT - NO!
run_duration is Elapsed time in the execution of the job or step in HHMMSS format (int) (from technet.microsoft).
Pay attention, that it is not always has all the 6 digits: 00:10:46 is 1046.
So, we need to cast it:
Cast to seconds:
146 = one minute and 46 seconds = 106 seconds:
select
( (run_duration % 100) -- Seconds
+ 60 * ((run_duration /100) % 100) -- + minutes to seconds
+ 3600 * ((run_duration / 10000) % 100) -- + Hours to seconds
) as Duration_SEC
from msdb.dbo.sysjobhistory
Cast to 'HH:MM:SS' format:
146 = 00:01:46
select
(CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6 ),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END) as Duration
from msdb.dbo.sysjobhistory
No comments:
Post a Comment