Tell me a step failed, but finish the job anyway: step-level failure notifications in SQL Agent

#The problem

A nightly job has six steps: back up five databases, then clean up old files. Step 3 fails, but you set On failure: go to next step so the other backups still run. The job finishes with success, so the job-level failure notification never fires.

#Pattern 1: a notify step on the failure path

Insert a notification step after each important step. On failure, route to it. On success, skip it.

Step 1  Backup DB1        success → step 3   failure → step 2
Step 2  Notify DB1 failed success → step 3   failure → step 3
Step 3  Backup DB2        success → step 5   failure → step 4
Step 4  Notify DB2 failed ...

The notify step:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = N'DBA Mail',
     @recipients   = N'dba-team@example.com',
     @subject      = N'$(ESCAPE_SQUOTE(SRVR)): step failed in job $(ESCAPE_SQUOTE(JOBNAME))',
     @body         = N'A step failed. Check job history for details.';

Agent tokens such as $(ESCAPE_SQUOTE(JOBNAME)) are replaced at run time. Simple, but it doubles the number of steps.

#Pattern 2: one final "check" step

Let every step continue on failure, then add a last step that reads the current run's history and fails if any step failed:

DECLARE @job_id uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_SQUOTE(JOBID)));
DECLARE @start datetime = (SELECT MAX(start_execution_date) FROM msdb.dbo.sysjobactivity WHERE job_id = @job_id);

IF EXISTS (
  SELECT 1 FROM msdb.dbo.sysjobhistory h
  WHERE h.job_id = @job_id AND h.step_id > 0 AND h.run_status = 0
    AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @start)
  RAISERROR (N'One or more steps failed in this run - see job history.', 16, 1);

Set this step's failure action to Quit the job reporting failure. The job now fails, and the normal job-level notification fires, while every step still ran. This is usually the cleanest pattern.

Warning

Remember to set Quit reporting failure on the check step. If it continues as "success", you're back where you started.

#Pattern 3: handle errors inside the step

For T-SQL steps that loop over databases, catch errors per item, log them, and raise once at the end:

DECLARE @errors int = 0, @db sysname;
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;
OPEN dbs; FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    DECLARE @sql nvarchar(max) = N'BACKUP DATABASE ' + QUOTENAME(@db) + N' TO DISK = N''B:\Backup\' + @db + N'.bak'' WITH COMPRESSION, CHECKSUM, INIT;';
    EXEC (@sql);
  END TRY
  BEGIN CATCH
    SET @errors += 1;
    PRINT CONCAT(@db, ': ', ERROR_MESSAGE());
  END CATCH
  FETCH NEXT FROM dbs INTO @db;
END
CLOSE dbs; DEALLOCATE dbs;
IF @errors > 0 RAISERROR (N'%d database(s) failed - see step output.', 16, 1, @errors);

Maintenance solutions such as Ola Hallengren's scripts already work this way.

#Which to choose

#Prevent it next time

• Treat "On failure: go to next step" as a design decision that needs a final check step.
• Review jobs whose steps continue on failure:

SELECT j.name, s.step_id, s.step_name
FROM msdb.dbo.sysjobsteps s JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
WHERE s.on_fail_action IN (3, 4);   -- go to next step / go to step n