Agent Jobs

Get Job History With TSQL

Here’s some quick SQL Logic which basically shows you the job history, with step history and step durations.

use msdb;
set nocount on

select
	'job_name'		=	sj.name
-- when step_id is zero '0', then you are seeing the final report of the job status.
,	'step_id'		=	sjh.step_id
,	'step_name'		=	case sjh.step_name
							when '(Job outcome)' then 'Job Completed'
							else sjh.step_name
						end
,	'status'		=	case
							when sjh.run_status = 0 then 'Failed'
							when sjh.run_status = 1 then 'Succeeded'
							when sjh.run_status = 2 then 'Retry'
							when sjh.run_status = 3 then 'Canceled'
							when sjh.run_status = 4 then 'In-Progress'
							when sjh.run_status = 5 then 'unknown'
						end
,	'start_time'	=	left(msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time), 19)
-- duration is a conversion of seconds to: Days, Hours, Minutes, Seconds                
,	'duration'		= 
						right('0' + cast(sjh.run_duration	/60/60/24%30	as varchar),2)	+ ':' +
						right('0' + cast(sjh.run_duration	/ 3600			as varchar),2)	+ ':' +
						right('0' + cast((sjh.run_duration	/ 60) % 60		as varchar),2)	+ ':' +
						right('0' + cast(sjh.run_duration	% 60			as varchar),2)
from        
	sysjobs sj inner join  sysjobhistory sjh on sjh.job_id = sj.job_id
order by
	sjh.instance_id, sjh.run_date, sjh.run_time asc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s