What is a Good Way to Quickly Disable SQL Agent Job Schedules?

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
SET     ss.[enabled] = 0
OUTPUT  INSERTED.schedule_id,
        sj.job_id,
        sj.name
INTO    @updated_schedules
FROM    msdb.dbo.sysjobs sj
        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
WHERE   sj.name LIKE '<pattern>'
           
DECLARE @schedule_id    int,
        @job_id         uniqueidentifier,
        @job_name       sysname

DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
    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
DEALLOCATE 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.

This entry was posted in Utilities and tagged . Bookmark the permalink.

2 Responses to What is a Good Way to Quickly Disable SQL Agent Job Schedules?

  1. Pingback: Database Disaster Recovery Revisited | B4PJS Blog

  2. Pingback: Database backups and the log chain for restore | B4PJS Blog

Leave a Reply

Your email address will not be published. Required fields are marked *