Get the list of all failed or canceled jobs from past 15 days

Job executing history are stored on the system table sysjobhistory in msdb database.  With the help of run_status flag we will get the list of failure or canceled jobs.

Status of the job execution:

·         0 = Failed

·         1 = Succeeded

·         2 = Retry

·         3 = Canceled

·         4 = In Progress

With the help of below sql script we will get the failed or canceled jobs

SELECT   j.[name] as [Job Name],           js.step_name,           jh.step_name,           jh.run_date,            jh.run_time,           jh.message,            jh.server  FROM     msdb.dbo.sysjobhistory jh           INNER JOIN msdb.dbo.sysjobs j             ON jh.job_id = j.job_id           INNER JOIN msdb.dbo.sysjobsteps js             ON j.job_id = js.job_id            AND jh.step_id = js.step_id  WHERE    jh.run_status in( 0,3)          AND jh.run_date > replace(CONVERT(varchar, getdate()-15, 23),'-','')  ORDER BY jh.instance_id DESC

We will get the result as below.