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