View Extended SQL Agent Job History

Use this query to generate job and job step history; you can see this information in the SSMS GUI, but this query will allow you to better play with (read: analyze) the information. Also, if you need to configure SQL Server 2005 to keep more history than the default of 100 records per job, you can modify the history tab under SQL Agent properties in SSMS. Here is the query:


select b.name as job,
a.step_name as step,
convert(varchar(10), cast(str(a.run_date,8, 0) as datetime), 111) as date,
stuff(stuff(right('000000' + cast (a.run_time as varchar(6 ) ) ,6),5,0,':'),3,0,':') as [time],
a.run_duration as duration_seconds,
a.run_duration / 60 as duration_minutes,
case         a.run_status
when 0 then 'failure'
when 1 then 'success'
when 2 then 'retry'
when 3 then 'cancelled'
when 4 then 'active'
end as disposition,
a.message as details
from         msdb.dbo.sysjobhistory a
inner join         msdb.dbo.sysjobs b
on b.job_id = a.job_id
-- {optionally, to filter by job name} where b.name = 'MyJobName'
order by         b.name,
a.run_date,
a.run_time