re: Getting Job History Correctly

Jack Donnell — Fri, 02 Mar 2012 06:07:11 GMT

Here is a query to use for history. Has some bugs with getting some date history:

SELECT SERVERPROPERTY('ServerName') [ServerName/Instance]

,job_name

,CASE WHEN step_id = 0 then 'Total Run Time' else CAST(step_id as varchar(10))End as step_id

,step_name

,datename(DW,run_datetime)StartDate

,run_datetime

,run_datetime + CASE WHEN run_duration = '00:00:0*' then '00:00:00' ELSE run_duration END [run_end_time]

,CASE WHEN run_duration = '00:00:0*' then '00:00:00' ELSE run_duration END run_duration

,RTRIM(SUBSTRING([Results], 1, 18)+'...') as [Results]

,Run_status

FROM

(

SELECT job_name,step_id,step_name, run_datetime,

SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +

SUBSTRING(run_duration, 5, 2) AS run_duration

,Run_status

,[Results]

FROM

(

SELECT DISTINCT

j.name as job_name, step_name,step_id,run_date as rd,

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)

,Run_status

,h.message [Results]

FROM msdb..sysjobhistory h

inner join msdb..sysjobs j

on h.job_id = j.job_id

) t

WHERE

--job_Name = 'MY IMPORTANT JOB' and -- Specific Job name

--step_id <> '0' AND -- Show step completion times

step_id = 0 AND -- Show only the run times

--step_id = 1 AND -- Show specific step

run_datetime > CONVERT(varchar(10),GetDate()-1,121) ---Current Day

-- run_datetime >'2012-02-13 02:40:00.000' and run_datetime<'2012-02-13 03:14:00.000' --Date Range

and run_status = 1 -- Successful(1), Failure(0)

ORDER BY job_name, step_id, run_datetime;