Finding SQL Server’s job execution history using TSQL

Often we need to compare how long a job took to execute in past. While GUI is a good tool for finding this information, it may be inefficient if we need to retrieve data for multiple jobs and/or for multiple times.
Below code (TSQL Script) will return Job Name, Execution date and execution duration for a specific job. Also please note that this report will generate one row for each step of the job. So for example if your job has 5 steps, you will get 5 steps for that particular execution of that job mentioning how much time each step’s execution has taken.

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
   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
where job_name =  <Job Name>   -- Example: 'Full Backups.Subplan_1'
order by run_datetime desc

Note: This code is tested on SQL 2000/2005/2008.


Note: You may omit where clause if you want this report for all jobs of the instance.