Find Failed Jobs in 24 Hours

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles