One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday.
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)
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.