Introduction
I recently blogged about a Stored Procedure to Sequentially Run SQL Agent Jobs and have been meaning to blog about this script as a follow-up because this is very helpful for converting from SQL job schedules to sequential job schedules while still having a back out path. The question of how to disable SQL jobs came up on #SqlHelp yesterday so I figured it was time to cover this script.
Background
When I looked at switching over to running my maintenance jobs in a sequential manner rather than fussing with individual job schedules it seemed like a daunting task. I had to have a way to cover a lot of ground fast so manually via Management Studio was out and I needed to be able to quickly rollback so a flexible script was definitely a must.
I decided to write a script to disable the job schedules on all of my maintenance jobs. I figured this was the easiest way to cover the most ground and if things went bad I could just change the script to turn the enabled flag back on and re-run it.
The Script
This script has 2 major parts, disable the job schedules then notify SQL Agent of the change. The script begins by declaring a table variable (NOTE: I started out with a regular #temp table here but got column definition errors that were corrected by changing to a table variable. I have not had time to research why this happened.) to store the changed records in, allowing SQL Agent to be notified of only the job schedules that have been changed.
I could have just notified on all jobs but this invites the possibility of “fixing” the schedule on a job that had been broken by some other scripted update. Not wanting to take the chance of starting SQL Agent jobs that have not run in a while I opted to carefully limit the scope of the notifications.
Once I have the results it is as simple as opening a cursor and calling sp_sqlagent_notify for each job schedule that has been updated.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | USE msdb DECLARE @updated_schedules table schedule_id int, job_id uniqueidentifier, job_name sysname UPDATE ss sj.job_id, sj.name INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id INNER JOIN msdb.dbo.sysschedules ss ON sjs.schedule_id = ss.schedule_id @job_id uniqueidentifier, @job_name sysname DECLARE csr CURSOR FAST_FORWARD READ_ONLY SELECT schedule_id, job_id, job_name FROM @updated_schedules OPEN csr FETCH NEXT FROM csr INTO @schedule_id, @job_id, @job_name WHILE @@FETCH_STATUS <> -1 BEGIN
EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N'S', @job_id = @job_id, @schedule_id = @schedule_id, @action_type = N'U'
PRINT 'Called sp_sqlagent_notify for ' + @job_name
FETCH NEXT FROM csr INTO @schedule_id, @job_id, @job_name
END CLOSE csr |
Conclusion
As usual, I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
