>>Script Language and Platform: T-SQL
This script looks for all failed jobs in the last day.
Author: Akhamie Patrick
/*The next Script performs the same function as the one above but it displays failure date, job name, step id, step name and error message.
I personally like this one alot, plus, to modify the timeline, simply adjust the day-* field in the last line of the query. A lot better than calculating hours dont you think?
Written by Akhamie Patrick
Date written: 10/11/2011*/
SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose… Disclaimer Continued
Back to Database Journal Home