Rudy Panigas (1/6/2010)
Excellent script! Keep up the great work!Question for you. Would you know if you can display the length of each job too? I would be nice to see how long the job took to complete from the start time.
This would make a good addition and you could then collect this information from all your server to ensure of jobs are overlapping in their execution.
Rudy
How about this script:
select\tj.job_id, Replace(j.name, '''', '') As [name],\t\tConvert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')) AS [run_start], CASE len(jh.run_duration) WHEN 1 THEN cast('00:00:0' + cast(jh.run_duration as char) as char (8)) WHEN 2 THEN cast('00:00:' + cast(jh.run_duration as char) as char (8)) WHEN 3 THEN cast('00:0' + Left(right(jh.run_duration,3),1) +':' + right(jh.run_duration,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(jh.run_duration,5),1) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) WHEN 6 THEN cast(Left(right(jh.run_duration,6),2) +':' + Left(right(jh.run_duration,4),2) +':' + right(jh.run_duration,2) as char (8)) END as [run_duration],\t\tDateAdd(second, jh.run_duration, Convert(DateTime, CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'))) AS [run_end],\t\tjh.message,\t\tjh.run_status from msdb..sysjobs j (nolock) inner join msdb..sysjobhistory jh (nolock) on jh.job_id = j.job_id where jh.step_id = 0 order by j.name, jh.run_date desc