Job Steps That Fail While the Agent Job Reports Success

Job steps that fail can hide behind a green job. SQL Server Agent shows Succeeded when the last route succeeds, even if an earlier step failed. That is not a bug. It is routing.

The green job and the red step

Here is how it goes. Monday morning, someone asks why the sales table is empty. You open the history of the nightly job. It is green. Every night, green.

Then you expand the job and find that step 1, the data load, failed at 2 AM. The job was set to continue, so step 2 ran and sent a happy “done” email. The job did what it was told. Nobody told it that step 1 mattered.

Build a job that fails on purpose

Let me show it for real. The demo creates a server-level object, a job in msdb, and removes it at the end. Step 1 divides by zero, so it always fails. It retries once, right away, and then goes to the next step. Step 2 is harmless. The job has no schedule, so only I start it.

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoJobFailedStep')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoJobFailedStep', @delete_history = 1;

EXEC msdb.dbo.sp_add_job @job_name = N'DemoJobFailedStep';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoJobFailedStep', @step_id = 1, @step_name = N'Load data',
     @subsystem = N'TSQL', @command = N'SELECT 1/0;',
     @on_success_action = 3, @on_fail_action = 3,
     @retry_attempts = 1, @retry_interval = 0;

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoJobFailedStep', @step_id = 2, @step_name = N'Send report',
     @subsystem = N'TSQL', @command = N'SELECT 1;',
     @on_success_action = 1, @on_fail_action = 2;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoJobFailedStep', @server_name = N'(local)';

Now read the settings in plain words instead of memorizing the numbers. Step 1 says “Go to next step” on failure. That is the leak.

SELECT s.step_id, s.step_name,
       CASE s.on_fail_action WHEN 1 THEN N'Quit with success' WHEN 2 THEN N'Quit with failure'
            WHEN 3 THEN N'Go to next step'
            WHEN 4 THEN N'Go to step ' + CONVERT(nvarchar(10), s.on_fail_step_id) END AS OnFail,
       CASE s.on_success_action WHEN 1 THEN N'Quit with success' WHEN 2 THEN N'Quit with failure'
            WHEN 3 THEN N'Go to next step'
            WHEN 4 THEN N'Go to step ' + CONVERT(nvarchar(10), s.on_success_step_id) END AS OnSuccess,
       s.retry_attempts, s.retry_interval
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE j.name = N'DemoJobFailedStep'
ORDER BY s.step_id;

Run it and read the history

This small helper starts a job, waits for it to finish, and prints the history rows of that one run. It checks every second and gives up after 60 seconds, so a stuck job cannot hang your window.

CREATE OR ALTER PROCEDURE dbo.RunJobAndWait @JobName sysname
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @JobId uniqueidentifier = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName);
    DECLARE @LastId int = (SELECT ISNULL(MAX(instance_id), 0) FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId);
    DECLARE @Seconds int = 0;

    EXEC msdb.dbo.sp_start_job @job_id = @JobId;
    WHILE @Seconds < 60 AND NOT EXISTS
          (SELECT 1 FROM msdb.dbo.sysjobhistory WHERE job_id = @JobId AND step_id = 0 AND instance_id > @LastId)
    BEGIN
        WAITFOR DELAY '00:00:01';
        SET @Seconds += 1;
    END;

    SELECT h.step_id, h.step_name, h.run_status, h.retries_attempted,
           CASE WHEN h.step_id = 0 THEN LEFT(h.message, CHARINDEX(N'.', h.message))
                ELSE SUBSTRING(h.message, CHARINDEX(N'. ', h.message) + 2, 65) END AS message
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.job_id = @JobId AND h.instance_id > @LastId
    ORDER BY h.instance_id;
END;

EXEC dbo.RunJobAndWait N'DemoJobFailedStep';

Read it from the top. Step 1 shows up twice. The first row has run_status 2, which is a retry. The second has run_status 0, a failure, with retries_attempted 1. The message says divide by zero. Step 2 ran and succeeded. The last row, step_id 0, is the job outcome. Its run_status is 1 and the message says the job succeeded. A failed step, and a green job.

Pair each failed step with its outcome

Nobody wants to expand every job by hand. This query takes each failed step and looks at the next outcome row of the same job. If that outcome succeeded, the failure was hidden.

SELECT j.name AS JobName, h.instance_id AS FailedStepHistoryId, h.step_id, h.step_name,
       o.instance_id AS OutcomeHistoryId, o.run_date, o.run_time
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
CROSS APPLY (SELECT TOP (1) x.instance_id, x.run_status, x.run_date, x.run_time
             FROM msdb.dbo.sysjobhistory AS x
             WHERE x.job_id = h.job_id AND x.step_id = 0 AND x.instance_id > h.instance_id
             ORDER BY x.instance_id) AS o
WHERE j.name = N'DemoJobFailedStep'
  AND h.step_id > 0 AND h.run_status = 0 AND o.run_status = 1
ORDER BY o.instance_id DESC, h.step_id;

One row comes back: the failed step 1 and the outcome that succeeded. The retry row is not listed, because its status is 2, not 0. I match on instance_id and the job, never the calendar date. Runs can overlap a day, and a run can cross midnight.

To scan your whole server, remove the job-name line. Take each row to the job owner. Some are intended: a probe step whose failure sends the job down a recovery path. Others are leaks.

Fix the leak and run it again

If the step is mandatory, change its failure action to quit with failure. Then run the job again.

EXEC msdb.dbo.sp_update_jobstep @job_name = N'DemoJobFailedStep', @step_id = 1, @on_fail_action = 2;

EXEC dbo.RunJobAndWait N'DemoJobFailedStep';

Step 1 still fails after its retry, but now there is no step 2 row. The outcome row shows run_status 0 and says the job failed. The job is red, which is what you wanted. Test a good run too before you trust the change.

Even so, keep a step-level alert for critical steps. Label it by consequence, because a skipped load and a slow step deserve different phone calls. Finally, clean up.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoJobFailedStep', @delete_history = 1;
DROP PROCEDURE IF EXISTS dbo.RunJobAndWait;

Green only means the last route succeeded, so look at the steps too.

A successful job is not proof of successful steps, it is a routing outcome.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.