Retry SQLAgent Job

  • Hello.

    Does anyone know how I can retry a job should any one of it's steps fail ? To clarify, I don't want to retry the job step but the whole job.



  • If you are using the GUI, in the advanced tab, see the "On failure action" dropdown. In addition to the more common "Quit the job reporting failure" or "Quit the job reporting success", you can tell the job to go to any step in the job. So tell it to go to step 1.

    You will need to set the failure action for every step.

    You might need to set retry attempts for step 1 to account for failure in step 1 -- I haven't tested to see if it will go to itself based on failure action.

  • What is the cause of the failure?  Are you attempting to prevent the failure, and handle it properly?  That may require a lot of work.

    If you use "go to step X on failure", be aware that you may be creating an endless loop.  Depending upon the number of steps, and what you want to do after each one, this can also get pretty complex.  I've queried the sysjobhistory table, and if the job was executed x number of times within a certain period of time, stopped it.

    Do you always want to re-run the entire job in the event of a failure on any step?  If so, you can create an alert to trap the error, and execute sp_start_job as the response to the alert.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply