Querying SQL Agent Job History

Really useful SQL if you ever need to use Job history to build graphs etc of job durations.

select DISTINCT sysjobs.name, 
sysjobhistory.step_id, 
sysjobhistory.run_time, 
sysjobhistory.run_date, 
(sysjobhistory.run_duration/60) as 'Minutes',
CASE sysjobhistory.run_status 
WHEN 1 THEN 'Success'
WHEN 3 THEN 'Cancelled'
END,
CASE LEN(sysjobhistory.run_time) 
            when NULL then cast (('N/A')as char (8))
            when 1 then cast(('00:00:0') + RIGHT(sysjobhistory.run_time,1) as char (8))
            when 2 then cast(('00:00:')
                + Left(sysjobhistory.run_time,2) as char(8))
            when 3 then cast('00:0' 
                + Left(right(sysjobhistory.run_time,3),1)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
            when 4 then cast('00:'
                + Left(right(sysjobhistory.run_time,4),2)
                +':' + Left(right(sysjobhistory.run_time,2),4)as char (8))
            when 5 then cast('0' + Left(right(sysjobhistory.run_time,5),1) 
                +':' + Left(right(sysjobhistory.run_time,4),2)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
            when 6 then cast(Left(right(sysjobhistory.run_time,6),2) 
                +':' + Left(right(sysjobhistory.run_time,4),2)  
                +':' + right(sysjobhistory.run_time,2) as char (8))
        end
from sysjobs, sysjobhistory
where sysjobs.job_id = sysjobhistory.job_id
and sysjobs.name = 'job_name'
--and sysjobhistory.step_id = 3
order by run_date ASC, 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

%d bloggers like this: