Ramblings of a DBA

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

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        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 |

Feedback

Gravatar

# 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
Gravatar

# 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,
sja.run_requested_date,
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