Recently, a customer approached me with a request to create a custom metric for SQL Monitor that would alert them on job steps that failed, even if the overall job succeeded. This got me thinking about how to determine if a job step failed in SQL Server. In this blog post, I will walk you through the process of monitoring job step failures and provide you with a query that can be used to accomplish this task.

First, let’s take a look at the query that forms the basis of our solution:

WITH cteJobStep (Job_ID, Step_Name) AS (
    SELECT jh.job_id, jh.step_name AS JobStepName
    FROM msdb.dbo.sysjobhistory jh
    INNER JOIN msdb.dbo.sysjobactivity ja ON jh.job_id = ja.job_id
    WHERE jh.run_status = 0 -- step failed
    AND jh.step_id != 0 -- exclude overall job
    -- add any additional filters here if needed
)
SELECT COUNT(*) AS Failures
FROM cteJobStep c

This query utilizes the sysjobhistory and sysjobactivity tables in the msdb database to identify job steps that have failed. We join these two tables on the job_id column, which serves as the primary key. By filtering for steps with a step_id greater than 0, we exclude the overall job and focus only on individual steps. A run_status of 0 indicates a failure, while a value of 1 represents success.

In order to capture data for all jobs, I modified the original query provided by Kendra. By removing the condition that limits the query to a specific job ID, we can now retrieve information for all jobs. Additionally, I retained the part of the query that ensures we only consider the most recent execution of each job.

The outer query simply counts the number of failures returned by the CTE and presents it as the result. This count gives us an indication of how many job steps have failed during their latest execution.

Now that we have a query to identify job step failures, we can incorporate this into SQL Monitor or any other monitoring tool to receive alerts when failures occur. By proactively monitoring job steps, we can quickly address any issues and ensure the smooth operation of our SQL Server environment.

I hope you found this blog post helpful in understanding how to monitor job step failures in SQL Server. Feel free to leave any comments or questions below.