How to rerun the SQL Agent job step automatically in case of failure?


How to rerun the SQL Agent job step automatically in case of failure?

Take an example of long running the job with the multiple steps where each steps has dependency of the previous steps successful execution. In case if any of the job step fails, entire job will fail and DBA has to manually run the job to complete it and job will run from the beginning.

There could be multiple reason of the job failure for example deadlock, network issue, requested backup file is not available as backup is still in progress etc.

You can avoid this situation using he job step advanced option Retry Attempts and Retry Interval (minutes)

Before the job step considered to have failed, you can set the number of times that the job step should be repeated using “Retry attempts” option. You can also use the Retry interval (minutes) option the number of minutes interval between retry attempts.

Default value of “Retry attempts” and “Retry interval (minutes)” is 0

For example if I will specify the “Retry attempts” to 3 and “Retry interval (minutes)” to 15 minutes, job step will re-run of up to 3 failure at 15 minutes interval.

Capture

Advertisements

One thought on “How to rerun the SQL Agent job step automatically in case of failure?

  1. mirindan2016

    Hello,

    Quick question if you don`t mind.
    If you set the Retry interval (minutes) to the default value 0 the retry attempt will start without delay?

    Thanks in advance for the answer!

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s