Share via

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

  • Article

 

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

  • Anonymous
    August 10, 2010
    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

  • Anonymous
    August 10, 2010
    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

  • Anonymous
    December 31, 2010
    www.sqlserver2005.de/Link.aspx link's broken...??

  • Anonymous
    December 31, 2010
    www.sqlserver2005.de/Link.aspx Sorry.... paste link again and it's still broken

  • Anonymous
    January 04, 2011
    Thanks for the heads up, I fixed the issue. -Jens

  • Anonymous
    May 02, 2012
    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.

  • Anonymous
    August 25, 2012
    The comment has been removed

  • Anonymous
    September 25, 2012
    I'm getting the following error, can you fix this please? Thanks. www.hvandenberg.net/ScriptDownloadError.jpg

  • Anonymous
    October 29, 2012
    The link is still broken. Please fix. Thanks.