Reading an Inherited SQL Server Agent Job: Step Flow, Retries and History

2026-11-10 · by Hannah Vernon · in SQL Server Agent

The job history says the nightly load failed. You open it. One step failed after its retries, the failure branch ran cleanly, the last step succeeded, and the job still says it failed, and the person who built it left two years ago.

A woman with a lantern and a map traces a route through a hedge maze of numbered stepping stones, with one path looping back and one stone walled off from every path.

Jobs like this grow one step at a time. Somebody adds a failure branch, somebody else adds a “go to step 9”, and a step that used to matter stops being reachable at all. Following those jumps by eye gets hard past a handful of steps. The step flow and the history are both in msdb. To test the queries, I built a small job with a retry, a failure branch, a loop and an unreachable step, and read it back with queries.

Where the Flow Is Stored

Two tables hold the flow. msdb.dbo.sysjobs.start_step_id is the step where execution begins.^[1] Each row in msdb.dbo.sysjobsteps has an action for success and an action for failure, plus a target step for each.^[2]

Code	on_success_action / on_fail_action
1	Quit with success (default on success)
2	Quit with failure (default on failure)
3	Go to the next step
4	Go to the step in `on_success_step_id` / `on_fail_step_id`

The same table holds retry_attempts and retry_interval. The interval is in minutes.^[3] In my test, a step with retries ran again after each failure, and its failure action only applied once the retries ran out.

The Test Job

I tested on one workstation with two instances: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). I created the job sat_multistep_demo on both, with its T-SQL steps running in a scratch database. The Agent service on the 2025 instance was stopped, and I left it that way. So the job only ran on 2019. The flow queries ran on both instances and returned the same rows.

Step	Name	Command	On success	On failure
1	Check source	`SELECT 1;`	go to step 3	quit with failure
2	Old archive step	`SELECT 1;`	next step	quit with failure
3	Load staging	`RAISERROR`, 2 retries, 1 minute apart	next step	go to step 5
4	Publish	`SELECT 1;`	quit with success	quit with failure
5	Log failure	`SELECT 1;`	go to step 6	go to step 3
6	Notify and fail	`SELECT 1;`	quit with failure	quit with failure

Nothing goes to step 2, and steps 3 and 5 can form a loop. Step 6 succeeds and then ends the job as a failure, which is one way to make a handled error still show as a failed job. I added each step with sp_add_jobstep, then set the go-to targets once all six steps existed. This is an excerpt from my setup script, and it writes to msdb, so don’t run it on a server you care about:

USE [msdb];

EXEC [msdb].[dbo].[sp_add_jobstep]
      @job_name = N'sat_multistep_demo'
    , @step_id = 3
    , @step_name = N'Load staging'
    , @subsystem = N'TSQL'
    , @database_name = N'sat_multistep'
    , @command = N'RAISERROR(N''Forced failure for the demo.'', 16, 1);'
    , @retry_attempts = 2
    , @retry_interval = 1
    , @on_success_action = 3
    , @on_fail_action = 2;

/* After all six steps exist: */
EXEC [msdb].[dbo].[sp_update_jobstep]
      @job_name = N'sat_multistep_demo'
    , @step_id = 3
    , @on_fail_action = 4
    , @on_fail_step_id = 5;

USE [msdb];

EXEC [msdb].[dbo].[sp_add_jobstep]

@job_name = N'sat_multistep_demo'

, @step_id = 3

, @step_name = N'Load staging'

, @subsystem = N'TSQL'

, @database_name = N'sat_multistep'

, @command = N'RAISERROR(N''Forced failure for the demo.'', 16, 1);'

, @retry_attempts = 2

, @retry_interval = 1

, @on_success_action = 3

, @on_fail_action = 2;

/* After all six steps exist: */

EXEC [msdb].[dbo].[sp_update_jobstep]

@job_name = N'sat_multistep_demo'

, @step_id = 3

, @on_fail_action = 4

, @on_fail_step_id = 5;

The Flow as a Readable List

The first query turns the codes into words. It also marks a go-to whose target step doesn’t exist, for a reason covered below.

USE [msdb];

SELECT
      [js].[step_id]
    , [js].[step_name]
    , CASE [js].[on_success_action]
          WHEN 1 THEN N'quit, report success'
          WHEN 2 THEN N'quit, report failure'
          WHEN 3 THEN N'next step'
          WHEN 4 THEN CONCAT(N'go to step ', [js].[on_success_step_id]
              , CASE WHEN NOT EXISTS (SELECT 1 FROM [msdb].[dbo].[sysjobsteps] AS [t]
                                      WHERE [t].[job_id] = [js].[job_id]
                                        AND [t].[step_id] = [js].[on_success_step_id])
                     THEN N' (MISSING)' ELSE N'' END)
      END AS [on_success]
    , CASE [js].[on_fail_action]
          WHEN 1 THEN N'quit, report success'
          WHEN 2 THEN N'quit, report failure'
          WHEN 3 THEN N'next step'
          WHEN 4 THEN CONCAT(N'go to step ', [js].[on_fail_step_id]
              , CASE WHEN NOT EXISTS (SELECT 1 FROM [msdb].[dbo].[sysjobsteps] AS [t]
                                      WHERE [t].[job_id] = [js].[job_id]
                                        AND [t].[step_id] = [js].[on_fail_step_id])
                     THEN N' (MISSING)' ELSE N'' END)
      END AS [on_failure]
    , [js].[retry_attempts]
    , [js].[retry_interval] AS [retry_interval_min]
    , CASE WHEN [js].[step_id] = [j].[start_step_id] THEN N'yes' ELSE N'' END AS [is_start]
FROM
    [msdb].[dbo].[sysjobs] AS [j]
INNER JOIN
    [msdb].[dbo].[sysjobsteps] AS [js]
        ON [js].[job_id] = [j].[job_id]
WHERE
    [j].[name] = N'sat_multistep_demo'
ORDER BY
    [js].[step_id];

USE [msdb];

SELECT

[js].[step_id]

, [js].[step_name]

, CASE [js].[on_success_action]

WHEN 1 THEN N'quit, report success'

WHEN 2 THEN N'quit, report failure'

WHEN 3 THEN N'next step'

WHEN 4 THEN CONCAT(N'go to step ', [js].[on_success_step_id]

, CASE WHEN NOT EXISTS (SELECT 1 FROM [msdb].[dbo].[sysjobsteps] AS [t]

WHERE [t].[job_id] = [js].[job_id]

AND [t].[step_id] = [js].[on_success_step_id])

THEN N' (MISSING)' ELSE N'' END)

END AS [on_success]

, CASE [js].[on_fail_action]

WHEN 1 THEN N'quit, report success'

WHEN 2 THEN N'quit, report failure'

WHEN 3 THEN N'next step'

WHEN 4 THEN CONCAT(N'go to step ', [js].[on_fail_step_id]

, CASE WHEN NOT EXISTS (SELECT 1 FROM [msdb].[dbo].[sysjobsteps] AS [t]

WHERE [t].[job_id] = [js].[job_id]

AND [t].[step_id] = [js].[on_fail_step_id])

THEN N' (MISSING)' ELSE N'' END)

END AS [on_failure]

, [js].[retry_attempts]

, [js].[retry_interval] AS [retry_interval_min]

, CASE WHEN [js].[step_id] = [j].[start_step_id] THEN N'yes' ELSE N'' END AS [is_start]

FROM

[msdb].[dbo].[sysjobs] AS [j]

INNER JOIN

[msdb].[dbo].[sysjobsteps] AS [js]

ON [js].[job_id] = [j].[job_id]

WHERE

[j].[name] = N'sat_multistep_demo'

ORDER BY

[js].[step_id];

step_id|step_name       |on_success          |on_failure          |retry_attempts|retry_interval_min|is_start
-------|----------------|--------------------|--------------------|--------------|------------------|--------
1      |Check source    |go to step 3        |quit, report failure|0             |0                 |yes
2      |Old archive step|next step           |quit, report failure|0             |0                 |
3      |Load staging    |next step           |go to step 5        |2             |1                 |
4      |Publish         |quit, report success|quit, report failure|0             |0                 |
5      |Log failure     |go to step 6        |go to step 3        |0             |0                 |
6      |Notify and fail |quit, report failure|quit, report failure|0             |0                 |

-------|----------------|--------------------|--------------------|--------------|------------------|--------

1 |Check source |go to step 3 |quit, report failure|0 |0 |yes

2 |Old archive step|next step |quit, report failure|0 |0 |

3 |Load staging |next step |go to step 5 |2 |1 |

4 |Publish |quit, report success|quit, report failure|0 |0 |

5 |Log failure |go to step 6 |go to step 3 |0 |0 |

6 |Notify and fail |quit, report failure|quit, report failure|0 |0 |

Past ten or so steps, I’d rather have a query trace the paths.

Walking the Flow with a Recursive CTE

The second query treats each step as a node and each success or failure action as an edge. Action 3 points to step_id + 1, action 4 points to the stored target, and actions 1 and 2 end the path. The walk starts at start_step_id and carries the path as a string such as /1/3/5/. When the next step is already in the path, the row is marked as a loop and the walk stops there. Any step that never appears in the walk can’t run from a normal start.

USE [msdb];

DECLARE @job_name sysname = N'sat_multistep_demo';

WITH [edges] AS
(
    SELECT
          [js].[job_id]
        , [js].[step_id]
        , [t].[target_step_id]
    FROM
        [msdb].[dbo].[sysjobsteps] AS [js]
    CROSS APPLY
    (
        VALUES
              ([js].[on_success_action], [js].[on_success_step_id])
            , ([js].[on_fail_action], [js].[on_fail_step_id])
    ) AS [v] ([action], [goto_step_id])
    CROSS APPLY
    (
        SELECT
            CASE [v].[action]
                WHEN 3 THEN [js].[step_id] + 1
                WHEN 4 THEN [v].[goto_step_id]
            END AS [target_step_id]
    ) AS [t]
    WHERE
        [t].[target_step_id] IS NOT NULL
)
, [walk] AS
(
    SELECT
          [j].[job_id]
        , [j].[start_step_id] AS [step_id]
        , CONVERT(nvarchar(4000), CONCAT(N'/', [j].[start_step_id], N'/')) AS [path]
        , CONVERT(bit, 0) AS [is_loop]
    FROM
        [msdb].[dbo].[sysjobs] AS [j]
    WHERE
        [j].[name] = @job_name
    UNION ALL
    SELECT
          [e].[job_id]
        , [e].[target_step_id]
        , CONVERT(nvarchar(4000), CONCAT([w].[path], [e].[target_step_id], N'/'))
        , CONVERT(bit, CASE WHEN [w].[path] LIKE CONCAT(N'%/', [e].[target_step_id], N'/%') THEN 1 ELSE 0 END)
    FROM
        [walk] AS [w]
    INNER JOIN
        [edges] AS [e]
            ON [e].[job_id] = [w].[job_id]
            AND [e].[step_id] = [w].[step_id]
    WHERE
        [w].[is_loop] = 0
)
SELECT
      [js].[step_id]
    , [js].[step_name]
    , CASE
          WHEN EXISTS (SELECT 1 FROM [walk] AS [w]
                       WHERE [w].[job_id] = [js].[job_id] AND [w].[step_id] = [js].[step_id])
              THEN N'reachable'
          ELSE N'UNREACHABLE'
      END AS [reachability]
    , COALESCE
      (
          (
              SELECT
                  STRING_AGG([w].[path], N'  ')
              FROM
                  [walk] AS [w]
              WHERE
                  [w].[job_id] = [js].[job_id]
                  AND [w].[is_loop] = 1
                  AND [w].[step_id] = [js].[step_id]
          )
        , N''
      ) AS [loop_paths_back_to_this_step]
FROM
    [msdb].[dbo].[sysjobs] AS [j]
INNER JOIN
    [msdb].[dbo].[sysjobsteps] AS [js]
        ON [js].[job_id] = [j].[job_id]
WHERE
    [j].[name] = @job_name
ORDER BY
    [js].[step_id];

USE [msdb];

DECLARE @job_name sysname = N'sat_multistep_demo';

WITH [edges] AS

(

SELECT

[js].[job_id]

, [js].[step_id]

, [t].[target_step_id]

FROM

[msdb].[dbo].[sysjobsteps] AS [js]

CROSS APPLY

(

VALUES

([js].[on_success_action], [js].[on_success_step_id])

, ([js].[on_fail_action], [js].[on_fail_step_id])

) AS [v] ([action], [goto_step_id])

CROSS APPLY

(

SELECT

CASE [v].[action]

WHEN 3 THEN [js].[step_id] + 1

WHEN 4 THEN [v].[goto_step_id]

END AS [target_step_id]

) AS [t]

WHERE

[t].[target_step_id] IS NOT NULL

)

, [walk] AS

(

SELECT

[j].[job_id]

, [j].[start_step_id] AS [step_id]

, CONVERT(nvarchar(4000), CONCAT(N'/', [j].[start_step_id], N'/')) AS [path]

, CONVERT(bit, 0) AS [is_loop]

FROM

[msdb].[dbo].[sysjobs] AS [j]

WHERE

[j].[name] = @job_name

UNION ALL

SELECT

[e].[job_id]

, [e].[target_step_id]

, CONVERT(nvarchar(4000), CONCAT([w].[path], [e].[target_step_id], N'/'))

, CONVERT(bit, CASE WHEN [w].[path] LIKE CONCAT(N'%/', [e].[target_step_id], N'/%') THEN 1 ELSE 0 END)

FROM

[walk] AS [w]

INNER JOIN

[edges] AS [e]

ON [e].[job_id] = [w].[job_id]

AND [e].[step_id] = [w].[step_id]

WHERE

[w].[is_loop] = 0

)

SELECT

[js].[step_id]

, [js].[step_name]

, CASE

WHEN EXISTS (SELECT 1 FROM [walk] AS [w]

WHERE [w].[job_id] = [js].[job_id] AND [w].[step_id] = [js].[step_id])

THEN N'reachable'

ELSE N'UNREACHABLE'

END AS [reachability]

, COALESCE

(

SELECT

STRING_AGG([w].[path], N' ')

FROM

[walk] AS [w]

WHERE

[w].[job_id] = [js].[job_id]

AND [w].[is_loop] = 1

AND [w].[step_id] = [js].[step_id]

)

, N''

) AS [loop_paths_back_to_this_step]

FROM

[msdb].[dbo].[sysjobs] AS [j]

INNER JOIN

[msdb].[dbo].[sysjobsteps] AS [js]

ON [js].[job_id] = [j].[job_id]

WHERE

[j].[name] = @job_name

ORDER BY

[js].[step_id];

step_id|step_name       |reachability|loop_paths_back_to_this_step
-------|----------------|------------|----------------------------
1      |Check source    |reachable   |
2      |Old archive step|UNREACHABLE |
3      |Load staging    |reachable   |/1/3/5/3/
4      |Publish         |reachable   |
5      |Log failure     |reachable   |
6      |Notify and fail |reachable   |

step_id|step_name |reachability|loop_paths_back_to_this_step

-------|----------------|------------|----------------------------

1 |Check source |reachable |

2 |Old archive step|UNREACHABLE |

3 |Load staging |reachable |/1/3/5/3/

4 |Publish |reachable |

5 |Log failure |reachable |

6 |Notify and fail |reachable |

Step 2 is unreachable, and the path /1/3/5/3/ is the loop. The loop only runs if step 3 fails, then step 5 fails as well. Agent accepted that definition without a warning, and I didn’t let it run. I don’t know whether Agent would ever break out of it on its own, so I’d check each loop by hand.

The CTE lists every path, not every step once. If a step’s success and failure actions point to the same step, its paths appear twice. A job with many branches can produce a lot of paths. The server-wide default limit for recursion is 100 levels, so a very long job may need OPTION (MAXRECURSION n) on the final SELECT.^[4] STRING_AGG needs SQL Server 2017 or later.^[5]

Go-To Targets Aren’t Checked

While testing the first query I pointed step 1 at a step that doesn’t exist:

USE [msdb];

EXEC [msdb].[dbo].[sp_update_jobstep]
      @job_name = N'sat_multistep_demo'
    , @step_id = 1
    , @on_success_action = 4
    , @on_success_step_id = 99;

USE [msdb];

EXEC [msdb].[dbo].[sp_update_jobstep]

@job_name = N'sat_multistep_demo'

, @step_id = 1

, @on_success_action = 4

, @on_success_step_id = 99;

Both instances returned Warning: Non-existent step referenced by @on_success_step_id. and saved the change anyway. On the 2025 instance the walk then marked every step after step 1 as unreachable, and the list showed go to step 99 (MISSING). On 2019 I started the job. Step 1 succeeded, and the job outcome row said The job failed. JobManager tried to run a non-existent step (99) for job sat_multistep_demo. I set the target back to 3 afterward. A warning in a deployment log is easy to miss, so I run the list query after any change to step numbers.

What sysjobhistory Records

I started the job with sp_start_job and read msdb.dbo.sysjobhistory once it finished. I’ve trimmed the output below to the first run, and it leaves out the instance_id and message columns to fit the page. run_status is 0 for failed, 1 for succeeded, 2 for retry, 3 for cancelled and 4 for in progress.^[6]

USE [msdb];

SELECT
      [h].[instance_id]
    , [h].[step_id]
    , [h].[step_name]
    , [h].[run_status]
    , [h].[retries_attempted]
    , CONVERT(varchar(19), [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]), 120) AS [run_start]
    , [h].[run_duration]
    , [h].[message]
FROM
    [msdb].[dbo].[sysjobhistory] AS [h]
INNER JOIN
    [msdb].[dbo].[sysjobs] AS [j]
        ON [j].[job_id] = [h].[job_id]
WHERE
    [j].[name] = N'sat_multistep_demo'
ORDER BY
    [h].[instance_id];

USE [msdb];

SELECT

[h].[instance_id]

, [h].[step_id]

, [h].[step_name]

, [h].[run_status]

, [h].[retries_attempted]

, CONVERT(varchar(19), [msdb].[dbo].[agent_datetime]([h].[run_date], [h].[run_time]), 120) AS [run_start]

, [h].[run_duration]

, [h].[message]

FROM

[msdb].[dbo].[sysjobhistory] AS [h]

INNER JOIN

[msdb].[dbo].[sysjobs] AS [j]

ON [j].[job_id] = [h].[job_id]

WHERE

[j].[name] = N'sat_multistep_demo'

ORDER BY

[h].[instance_id];

step_id|step_name      |run_status|retries_attempted|run_start          |run_duration
-------|---------------|----------|-----------------|-------------------|------------
1      |Check source   |1         |0                |2026-10-30 12:09:14|0
3      |Load staging   |2         |0                |2026-10-30 12:09:14|0
3      |Load staging   |2         |1                |2026-10-30 12:10:14|0
3      |Load staging   |0         |2                |2026-10-30 12:11:14|0
5      |Log failure    |1         |0                |2026-10-30 12:11:14|0
6      |Notify and fail|1         |0                |2026-10-30 12:11:14|0
0      |(Job outcome)  |0         |0                |2026-10-30 12:09:14|200

-------|---------------|----------|-----------------|-------------------|------------

1 |Check source |1 |0 |2026-10-30 12:09:14|0

3 |Load staging |2 |0 |2026-10-30 12:09:14|0

3 |Load staging |2 |1 |2026-10-30 12:10:14|0

3 |Load staging |0 |2 |2026-10-30 12:11:14|0

5 |Log failure |1 |0 |2026-10-30 12:11:14|0

6 |Notify and fail|1 |0 |2026-10-30 12:11:14|0

0 |(Job outcome) |0 |0 |2026-10-30 12:09:14|200

Each attempt at step 3 has its own row. The first two attempts have run_status 2, and the third has 0. retries_attempted counts up from 0, and the attempts are one minute apart. The message on the final row ends with NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.
Step 2 and step 4 have no rows. The history only records steps that ran.
The row with step_id 0 and step_name (Job outcome) is the job result. It was written last, but its run_date and run_time are the job’s start time. run_duration is in HHmmss form, so 200 means two minutes.
Every step after the retries succeeded, and the job outcome is 0 (failed). The message says The job failed. The Job was invoked by User CONTOSO\dbauser. The last step to run was step 6 (Notify and fail). Step 6 is the reason: it quits with failure when it succeeds.

I alert on the step_id 0 row, not the step rows. Monitoring SQL Server Agent Job Failures with SolarWinds DPA Custom Alerts builds a monitoring query on that row. If the outcome row shows a failure that nobody heard about, Unreported SQL Server Agent Job Failures covers the notification side.

Right after this run, sysjobsteps.last_run_retries for step 3 was 3. The documentation says that column holds the number of retry attempts in the last execution, and the history rows show 2.^[2] I go by the history rows.

Unreachable Doesn’t Mean Unused

sp_start_job takes a @step_name parameter to start a job at a step other than the start step.^[7] I changed step 3 to SELECT 1; and ran the job twice. The normal start ran steps 1, 3 and 4 and succeeded. Starting at Old archive step ran steps 2, 3 and 4, and the outcome message ended with The job was requested to start at step 2 (Old archive step).

So a step that the walk marks as unreachable may still be part of somebody’s manual recovery routine. Before deleting one, I’d search the history for outcome messages that mention a requested start step, and ask around.

Limits of This Test

I ran the job on SQL Server 2019 only. The Agent service on the 2025 instance was stopped, so on 2025 I tested the definitions, the warning and the queries, not the run-time behaviour.
I used T-SQL steps only, and didn’t test other subsystems such as CmdExec or PowerShell.
I didn’t run the loop.
The history query only sees rows still in sysjobhistory. If history has been purged, older runs won’t be there.

If you’ve untangled an inherited job with a step flow stranger than this one, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

dbo.sysjobs (Transact-SQL) - Microsoft Learn. Defines start_step_id as the ID of the step where execution begins. ↩
dbo.sysjobsteps (Transact-SQL) - Microsoft Learn. Lists the on_success_action and on_fail_action codes 1 to 4, their defaults, and the step ID columns used by code 4. ↩
sp_add_jobstep (Transact-SQL) - Microsoft Learn. States that retry_interval is the time in minutes between retry attempts. ↩
WITH common_table_expression (Transact-SQL) - Microsoft Learn. States that the server-wide default recursion limit is 100 and that MAXRECURSION can change it per statement. ↩
STRING_AGG (Transact-SQL) - Microsoft Learn. Applies to SQL Server 2017 (14.x) and later versions. ↩
dbo.sysjobhistory (Transact-SQL) - Microsoft Learn. Lists the run_status values, the retries_attempted column and the HHmmss format of run_duration. ↩
sp_start_job (Transact-SQL) - Microsoft Learn. Documents the @step_name parameter, the step at which to begin execution of the job. ↩

Tags: msdb, recursive-cte, SQL Server, SQL Server 2025, sql-server-2019, sql-server-agent, sysjobhistory, sysjobsteps

Reading an Inherited SQL Server Agent Job: Step Flow, Retries and History

Where the Flow Is Stored

The Test Job

The Flow as a Readable List

Walking the Flow with a Recursive CTE

Go-To Targets Aren’t Checked

What sysjobhistory Records

Unreachable Doesn’t Mean Unused

Limits of This Test

References

Like this:

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta

Reading an Inherited SQL Server Agent Job: Step Flow, Retries and History

Where the Flow Is Stored

The Test Job

The Flow as a Readable List

Walking the Flow with a Recursive CTE

Go-To Targets Aren’t Checked

What sysjobhistory Records

Unreachable Doesn’t Mean Unused

Limits of This Test

References

Share this:

Like this:

Related

Let us know your thoughts on this post Click here to cancel reply.

Search

Categories

Pages

Meta