Reusable Component – Script to Identify previous day job failure list.

/*This query gives previous day  job failure list*/

SELECT [Job Name],
         h.step_id [Step_ID],
         h.step_name [Step Name],
         h.run_date [Run Date],
         h.run_time [Run Time],
         h.sql_severity [SQL_Severity],
         h.message [Error Message]
FROM     msdb.dbo.sysjobhistory h
         INNER JOIN msdb.dbo.sysjobs j
         ON h.job_id = j.job_id
         INNER JOIN msdb.dbo.sysjobsteps s
         ON j.job_id = s.job_id
         AND h.step_id = s.step_id
WHERE    h.run_status = 0 — Failure
and CAST(CONVERT(VARCHAR, h.run_date) + ‘ ‘ + STUFF(STUFF(RIGHT(‘000000’+ CONVERT(VARCHAR,h.run_time),6),5,0,’:’),3,0,’:’) AS DATETIME)
between dateadd(DD, -01,getdate()) and getdate()
ORDER BY h.instance_id DESC  

  • January 3, 2018 | 20 views
  • Comments