Did your SQL Agent doze off and you don´t know which jobs to run afterwards ?


 

In some cases you might face the problem that SQL Server Agent Service didn’t come up or was stopped for reason. Jobs scheduled in the downtime will not run automatically when the Agent service comes up again, and that is a good thing. I often hear people saying, why isn’t that supposed to be the other way around? Shouldn’t the service revisit the schedules and see which ones he missed and run that ? The precise answer to that is – it depends.

Imagine you have jobs doing Index maintenance or doing some other resource intensive operations like aggregating data, running statistical calculations etc, which are normally planned in the off-peak hours. You don´t want them to run when the people need SQL Server to operate efficiently, right ? So in most cases, the answer is, no you really don´t want that. But imagine several processes being as important as the OLTP operations taking place on SQL Server. That would be a good thing to run afterwards.

If you have hundreds of jobs, you don´t want them to run all at once and you definitely don´t want to search them in SSMS and right click them –-> Start Job at Job step. That is what the script I created here is all about. It will grab the execution statistics from the activities log and create you an executable script. (For security reasons it comments all disabled jobs out). After executing the script, you will get the following scripts prepared for execution: (sample output from my computer for testing)

--JobName: MyJob (Last planned execution in the past 20100723 14:00)
/* Job disabled in the job repository */-- EXEC msdb.dbo.sp_start_job  @job_id = '1BD665F9-E302-4376-8D92-69F7CBB68391',@step_name = 'JebStep 1'

--JobName: syspolicy_purge_history (Last planned execution in the past 20100724 20:00)
 EXEC msdb.dbo.sp_start_job  @job_id = '0A47E5C3-F788-46EA-AC06-6396751BAD44',@step_name = 'Erase Phantom System Health Records.'

--JobName: syspolicy_purge_history (Last planned execution in the past 20100724 20:00)
 EXEC msdb.dbo.sp_start_job  @job_id = '0A47E5C3-F788-46EA-AC06-6396751BAD44',@step_name = 'Purge history.'

--JobName: syspolicy_purge_history (Last planned execution in the past 20100724 20:00)
 EXEC msdb.dbo.sp_start_job  @job_id = '0A47E5C3-F788-46EA-AC06-6396751BAD44',@step_name = 'Verify that automation is enabled.'


The script is available here.
-Jens
Comments (9)

  1. Cilvic says:

    Hi Jens,

    I was looking for exactly that question. I have a lot of routine tasks, which I would like the agent to catch up on as soon as it is back online.

    Can you tell me where to use this script (just on the SQL Server?).

    Is there another way you recommend to run this kind of daily update tasks?

    Thanks

    Cilvic

  2. JensS says:

    HI Cilvic,

    the script is mentioned to run on SQL Server and if you don´t know if a certain job should have run in between a downtime of the SQL Server Agent. As of my opinion no daily stuff, but it could run daily if you wanted to.

    -Jens

  3. Bill says:

    http://www.sqlserver2005.de/Link.aspx

    Sorry….

    paste link again and it's still broken

  4. JensS says:

    Thanks for the heads up, I fixed the issue.

    -Jens

  5. Interested in the code says:

    Jens i'm very interested in looking at your code for this, however I can't get the link to work.  Any chance I can get a new link?  Thanks -Luke.

  6. SQLDude says:

    I am getting this error when i try to run the script

    Msg 241, Level 16, State 1, Line 6

    Conversion failed when converting date and/or time from character string.

  7. Henk says:

    I'm getting the following error, can you fix this please? Thanks.

    http://www.hvandenberg.net/ScriptDownloadError.jpg

  8. Ben C says:

    The link is still broken. Please fix. Thanks.