Retrying a failed job step helps only when the underlying problem can clear. Setting retry attempts requires a bounded delay and work that remains correct when the entire step runs again.
Understand What Retry Attempts Repeat
SQL Server Agent retries the failed step, rather than continuing from the statement that failed. The step's command executes again under its configured subsystem and security context. Partial work from the prior attempt therefore matters.
Configured retry attempts represent additional executions after the initial execution. A setting of two permits up to three executions of that step. Retry interval is measured in minutes, including when the command is a T-SQL batch.
I review the transaction and external side effects before increasing this count. I also read the complete failed-step command rather than its short name. A name like Load data does not explain which rows already committed.
Use this feature on SQL Server editions with SQL Server Agent, including supported SQL Server 2025 installations. Azure SQL Database does not provide this instance Agent interface. Confirm the actual scheduler before applying an example copied from an Agent job.
Create a Controlled Retry Demonstration
Use an isolated test database and a dedicated job on a test instance. The counter below intentionally survives a failed step to make the retry sequence visible. It is demonstration state, rather than a recommended transaction design for a business load.
DROP TABLE IF EXISTS dbo.RetryState;
CREATE TABLE dbo.RetryState
(
TestId int NOT NULL PRIMARY KEY,
Attempts int NOT NULL
);
INSERT dbo.RetryState(TestId, Attempts) VALUES (1, 0);Capture the test database name before creating the job in msdb. The job has no schedule and is initially disabled to prevent scheduled execution. Appropriate job administration permissions and a running Agent service are prerequisites.
DECLARE @TestDatabase sysname = DB_NAME();
USE msdb;
DECLARE @JobId uniqueidentifier;
EXEC dbo.sp_add_job
@job_name = N'Retry Demonstration',
@enabled = 0, @job_id = @JobId OUTPUT;
EXEC dbo.sp_add_jobstep
@job_id = @JobId, @step_name = N'Controlled transient failure',
@subsystem = N'TSQL', @database_name = @TestDatabase,
@command = N'
UPDATE dbo.RetryState SET Attempts = Attempts + 1 WHERE TestId = 1;
IF (SELECT Attempts FROM dbo.RetryState WHERE TestId = 1) < 3
BEGIN
THROW 50001, ''Controlled retry demonstration.'', 1;
END;',
@on_success_action = 1, @on_fail_action = 2,
@retry_attempts = 0, @retry_interval = 0;
EXEC dbo.sp_add_jobserver @job_id = @JobId;The command fails while the counter remains below three. Running the command body alone three times in a test database gave two failures, then a success. Each failed execution leaves the committed counter increment available to the next execution. The example deliberately demonstrates why a retry can see effects left by an earlier attempt.
Do not create a duplicate job when rerunning the setup. Inspect the existing demonstration job and clean it up through the approved test procedure. Record the owner and effective execution context before starting any job manually.
Set Retry Attempts and the Interval
Update the selected step using sp_update_jobstep in msdb. The example allows two retries, each separated by a one-minute interval. Those values illustrate the settings rather than prescribing an operational standard.
USE msdb;
EXEC dbo.sp_update_jobstep
@job_name = N'Retry Demonstration',
@step_id = 1,
@retry_attempts = 2,
@retry_interval = 1;
SELECT j.name AS JobName, s.step_id, s.step_name,
s.retry_attempts, s.retry_interval,
s.on_success_action, s.on_fail_action
FROM dbo.sysjobs AS j
JOIN dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE j.name = N'Retry Demonstration';The failure action applies after the step exhausts its retry policy. Here, the job quits with failure if the final allowed execution fails. A success action of one ends the job successfully after the step succeeds.
A zero-minute interval permits immediate retries and can intensify a temporary overload. Choose the delay with the failure mode and recovery expectation in mind. Agent's fixed minute interval is not a sophisticated exponential-backoff policy.
The total job duration includes execution time and waiting between retries. Keep that total inside the operational deadline and next scheduled window. More retries can delay downstream work even when the job eventually succeeds.
Read Retry Evidence in Job History
Start the dedicated demonstration manually when ready to observe it. The start call dispatches the job and does not wait for its completion. Query job activity or history afterward rather than declaring success from dispatch alone.
EXEC msdb.dbo.sp_start_job @job_name = N'Retry Demonstration';
SELECT TOP (30)
h.instance_id, h.step_id, h.step_name,
h.run_date, h.run_time, h.run_duration,
h.run_status, h.retries_attempted,
h.sql_message_id, h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE j.name = N'Retry Demonstration'
ORDER BY h.instance_id DESC;History status two indicates a retry entry. Status zero indicates failure, one success, and three cancellation. Step zero is the job-level outcome, which must be distinguished from individual step attempts.
The run duration value is stored in an hours-minutes-seconds numeric format. It is not a plain number of elapsed seconds. Preserve its documented interpretation when building operational reports.
History is generally written when a step completes and is subject to retention limits. A missing in-progress row does not prove that nothing is running. Inspect current job activity when the retry is still underway.
Retry Temporary Failures Without Replaying Permanent Mistakes
A brief connectivity interruption or a deadlock can justify another attempt when the operation is safe to repeat. A malformed input file, permission failure, or schema mismatch requires correction. Repeating the same permanent mistake only delays a useful failure report.
Agent retries failed steps without classifying each error by business meaning. Use procedure-level logic when different failures need different policies. Preserve a final failure outcome so operational notifications can still reach the responsible owner.
Make repeated work idempotent where the business operation allows it. Use stable run identifiers, unique constraints, and transactional boundaries to prevent duplicate effects. External messages or files need their own reconciliation rules because database rollback does not erase them.
Do not swallow an exception after logging it if Agent must treat the step as failed. Re-throw the appropriate error after consistent cleanup. Returning success for a failed load prevents the configured retry and failure actions from working as intended.
Review committed partial work before approving a retry. A procedure can commit one batch and fail during another. The next attempt needs a supported resume or replay contract rather than an assumption that all prior work vanished.
Inventory Settings and Prove the Policy
The following query lists the retry settings for visible job steps. Use authorized access that covers the jobs under review. Inspect disabled jobs too, because their stored policies can matter when they are enabled later.
SELECT j.name AS JobName, j.enabled,
s.step_id, s.step_name, s.subsystem,
s.retry_attempts, s.retry_interval,
s.last_run_retries, s.on_fail_action
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;Can the step succeed after a temporary failure without duplicating completed business work? Test that question alongside a permanent failure that exhausts its attempts. Verify the final job outcome and the responsible notification path.
Reset the demonstration counter before repeating its controlled sequence. Remove only the dedicated job and test table after the exercise. Preserve operational evidence before cleanup if the exercise forms part of an approved recovery test.
Document retry attempts, interval, failure classification, and replay behavior together. A count without those supporting rules describes persistence, not reliability. The policy is complete when repeated execution and final failure both remain understandable.
Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Waiting for an Agent Job to Finish Before the Next Step Runs.
A retry is not a recovery guarantee, it is another complete execution that must remain safe and bounded.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
