Blog

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:

CREATE DATABASE SQLAgentRetryDemo
GO

USE SQLAgentRetryDemo
GO

CREATE TABLE dbo.DBStatus
	(
		Finished BIT NOT NULL
	)
GO

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:

IF NOT EXISTS (SELECT * FROM dbo.DBStatus WHERE Finished = 1) BEGIN
	;THROW 50000, 'Not ready... retrying...', 1
END

The job step should look like this:

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

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:

DELETE FROM dbo.DBStatus

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”:

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

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: