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.