Post reply

SQL Agent job does not continue after server restart

  • November 23, 2015 at 8:12 am

    #313369

    I have a SQL Agent job that runs for several hours in the middle of the night. The job spends most of its time looking for certain piece of data to appear. The job step that looks for this throws when the data is not found and is set to retry every 15 minutes. Once the data appears, the step succeeds and the rest of the job completes.

    Last weekend, the server was restarted while the job was in a retry wait. I believe that all of the servers are restarted once a month on a Saturday at 4 am. The job just stopped where it was. It didn't continue to retry after the restart and job history showed it was still running, but when I attempted to stop it, it wasn't actually running.

    How can I design this job so that it will pick up where it left off after a middle-of-the-night restart? Actually, it would be enough to re-run the job entirely after a restart if the job determines that it should have been running during that time frame.

  • November 23, 2015 at 8:27 am

    #1841738

    I don't know that it will continue where it left off.

    But did you verify that SQL Server Agent is set to restart automatically (at the Agent & service level) when the server gets restarted?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 23, 2015 at 8:40 am

    #1841743

    Guessing the problem is down to using WAITFOR DELAY '00:15' in the query to wait the 15 minutes.

    Is a logic change possible to the job?

    If so, my recommendation would be to have a control table with a flag which is reset at a specified time.

    Then the 15 minute loop comes in, check if the value is "No not done todays work", checks for the data, if it doesnt exist just end the job

    If it does exist, do what it needs to do and update the control table to say "Hey I've done the work today"

    Then schedule the job to run every 15 minutes instead of using a time loop in the query, that way if the agent stops and starts SQL knows to start the job every 15 minutes

  • November 26, 2015 at 8:27 am

    #1842521

    Theres a 'Schedule type' option in the SQL Agent to start a job automatically whenever the Agent starts...which it will when a server (or SQL itlsef for that matter) is restarted

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

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