Knowing particular SQL Server Agent job history in SQL Server

Posted by Bandi under Sql Server category on | Points: 40 | Views : 1282
USE msdb
SELECT JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
AND h.run_status <>1
WHERE like '%Jobname%'
ORDER BY h.run_date desc, h.run_time desc

--Sample Output
JobName	StepName	RunDate	        RunTime	        StepDuration	ExecutionStatus	MessageGenerated
JobName Step1 2014/12/19 20:56:19 934219 Cancelled Executed as user: UserName. The step was cancelled (stopped) as the result of a stop job request.

Comments or Responses

Login to post response