Finding Failed Jobs

How would you go about finding jobs that have failed and why would you want to look for these?  You might want to do this to ensure that you are being notified appropriately of job failures. For instance, after reviewing failed jobs, you may realize that there are job failures that no one knew about. Maybe this is occurring because the jobs are not set to send email. If that’s the case see my post here so you can fix that.

But how do you find these job failures in the first place?

1. Manually look at the job activity monitor for the SQL instance.

The job activity monitor is found under the SQL Server Agent node in the Object Explorer of SSMS.

Just double click it to open and you will see something like this. In this case I set up a job with an incomplete SELECT statement so that it would create a failure.

2. Query the msdb database.

SELECT
J.Name AS JobName
, J.description AS JobDescription
, H.step_id
, H.step_name
, msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
, H.sql_severity
, H.message
FROM sysjobs AS J
INNER JOIN sysjobhistory AS H ON J.job_id = H.job_id
INNER JOIN sysjobsteps AS JS ON J.job_id = JS.job_id AND H.step_id = JS.step_id
WHERE H.run_status = 0 --Failed jobs