Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


SQL Server Script to Display Job History

I was going through my scripts today and found one that I’d like to share. 

This SQL Server script will display job history.  The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. 

select job_name, run_datetime, run_duration
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

I know I stole the code from the inner-most derived table from someone, but I didn’t make a note of the source. I did a quick search for the source, but I came up with too many possibilities.

Print | posted on Wednesday, June 10, 2009 11:29 AM |



# re: SQL Server Script to Display Job History

Very cool script, thanks for sharing it. It identified some jobs I needed to tweak since they didn't need to be running every minute of every day (sheesh!)
6/17/2009 5:31 AM | Zack Jones

# re: SQL Server Script to Display Job History

This code above displays the job step history, which is not necessarily the result for the entire job. For one 'run' of a job, you will potentially get more than one result (if the timestamp differs between steps).

There are two solutions to this - either limit your inner derived table to step_id = 0
Or replace the entire thing with:

SELECT sj.name,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc
7/9/2009 5:22 PM | Simeon
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET