October 7, 2014

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you.

 USE Msdb  
 Declare @lastRunDate int =replace(CAST(getdate()as date),'-','')  
 SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed'  
 WHEN 1 THEN 'Succeded'  
 WHEN 2 THEN 'Retry'  
 WHEN 3 THEN 'Canceled'  
 END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ  
 ON SJH.job_id = SJ.job_id  
 where SJH.step_id = 0  
 and sjh.run_date =@lastRunDate  
 and SJH.run_status =0