You are currently viewing Automatic retries in a SQL Agent job
Microsoft SQL Server

Automatic retries in a SQL Agent job

Many SQL Agent jobs can be run automatically using the scheduler to run the job at specified times. But sometimes it would be better to run the job when specific criteria have been met, instead of at a specified time. For instance, when the data warehouse has finished loading.

This blog post will show how to create a job that starts and then waits / retries until given criteria are met.

For this demonstration, create a database with the following T-SQL:


USE SQLAgentRetryDemo

		Finished BIT NOT NULL

The next step is to create a job that checks if there is a dbo.DBStatus.Finished value. If not, it will wait for a minute and then try again. This will repeat 10 times. Both the number of waits between reties and the total number of retries are configurable.

Create a new job with the name “SQLAgentRetryDemo”. In the new job, create a job step with the name “Check if the job is ready to run”. Select the database SQLAgentRetryDemo and paste the following T-SQL command:

	;THROW 50000, 'Not ready... retrying...', 1

The job step should look like this:

SQL Agent Retry Demo
SQL Agent Retry Demo

Next, select the page Advanced. Here you can set the number of retry attempts as well as the retry interval in minutes:

SQL Agent Job Step Advanced
SQL Agent Job Step Advanced

Next, create another job step with the name “Start the actual job”. Here’s where the code for the actual job will be placed. The last statement here will be to delete the status to indicate that the job is run. Use the following T-SQL in this step:


Now the job can be run. Right click and select “Start job at step” and start at step 1.

If you wait a few minutes and check the “Job Activity Monitor”, you’ll see that the status is “Between retries”:

SQL Agent Between Retries
SQL Agent Between Retries

Also, if you right click the job and select “View History”, you can see each retry attempt:

SQL Agent Job History
SQL Agent Job History

Let’s simulate that the criteria we’re waiting for are met. Use the following T-SQL:

INSERT dbo.DBStatus (Finished) VALUES (1)

In the next retry, the job will finish with success. If the job goes through all retries and never does a successful retry, the complete SQL Agent job will fail.

Note however that there will be a warning sign in the history indicating that retries were made:

SQL Agent Retry Warnings In History
SQL Agent Retry Warnings In History

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

Leave a Reply