List Sql Server jobs and their owners and duration history

use msdb
go
select name, suser_sname(sysjobs.owner_sid) as owner from msdb..sysjobs
go

select sj.name,sjh.run_status,case sjh.run_status
						when 1 then 'Sucess'
						when 2 then 'Retry'
						when 3 then 'Cancelled'
						when 4 then 'In Progress'
						when 0 then 'Failed'
						End
						 as 'Run Status',sjh.run_date as 'Run Date',
CASE len(run_time)
		WHEN 1 THEN cast('00:00:0'
				+ cast(run_time as char) as char (8))
		WHEN 2 THEN cast('00:00:'
				+ cast(run_time as char) as char (8))
		WHEN 3 THEN cast('00:0' 
				+ Left(right(run_time,3),1)  
				+':' + right(run_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(run_time,4),2)  
				+':' + right(run_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(run_time,5),1) 
				+':' + Left(right(run_time,4),2)  
				+':' + right(run_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(run_time,6),2) 
				+':' + Left(right(run_time,4),2)  
				+':' + right(run_time,2) as char (8))
	END as 'Start Time',
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..sysjobs sj
join sysjobhistory sjh on sj.job_id=sjh.job_id
where name like 'NameString%'
and sjh.run_date>='yyyymmdd' and sjh.step_id = 0
order by run_duration

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.