A user had a unique issue where their system would have dynamically changing job names and schedules, but they need to disable and re-enable them during maintenance. Obviously, this is a huge headache.
I made a recommendation that they should ultimately create a list of currently enabled jobs that had a schedule using a system query.
SELECT * FROM MSDB.dbo.sysschedules ss INNER JOIN msdb.dbo.sysjobschedules jss ON jss.schedule_id = ss.schedule_id WHERE ss.enabled = 1;
The code above returns all schedules that are paired to a job that are enabled. The enabled = 1 flag and the inner join to the sysjobschedules table are what dictate those filters.
They can then store this list into a physical table and execute a loop or cursor on it that executes a procedure that disables those jobs. Once their maintenance is complete, they can run another script that goes back through that list and re-enables them. This basically “stops the SQL scheduler”.
It doesn’t ACTUALLY stop the scheduler, the SQL Agent in SQL Server RUNS the scheduler. The only way to REALLY stop it, is to stop the SQL Agent service. This was something they didn’t want to do and my proposed solution seems like the best fit for the problem.
At the time I was unable to provide a full code solution, but as personal life projects free up I am able to circle back and create a full solution.
There are two scripts below. The first script creates a table to store our information about the jobs and schedules. It then inserts the information from our above query, limiting results to only enabled jobs with a schedule. It then executes a loop which goes through and executes the sp_update_schedule procedure. This disables those schedules from the jobs that are enabled, keeping them from running.
The second script looks at that table and goes back through looping it and re-enables the schedules for that table.
!!!CAUTION!!!
Test this in a test environment before touching production. (Like you should always do before taking erroneous internet code and running with it.)
Script One – Disabling Schedules
USE MSDB; /************************************************************* Checking for history table. Creating it if it doesn't exist. *************************************************************/ IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL BEGIN CREATE TABLE [dbo].[JobsEnabledTracker]( [Id] [INT] IDENTITY(1, 1) NOT NULL, [job_id] [UNIQUEIDENTIFIER] NULL, [schedule_id] [BIGINT] NULL, [enabled] [BIT] NULL); END; IF EXISTS ( SELECT 1 FROM [dbo].[JobsEnabledTracker] WHERE [enabled] = 1 ) OR ( SELECT COUNT(*) FROM [dbo].[JobsEnabledTracker] ) = 0 BEGIN PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.'; /*********************** Clear out history table ***********************/ PRINT 'Truncating history table: dbo.JobsEnabledTracker'; TRUNCATE TABLE [dbo].[JobsEnabledTracker]; PRINT 'Inserting records into history table: dbo.JobsEnabledTracker'; /****************************** Add in values to history table ******************************/ INSERT INTO [dbo].[JobsEnabledTracker] ( [job_id], [schedule_id], [enabled] ) SELECT [jss].[job_id], [jss].[schedule_id], 1 AS 'enabled' FROM [msdb].[dbo].[sysschedules] AS [ss] INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id] WHERE [ss].[enabled] = 1; /********************************************************************************** Table variable to hold schedules and jobs enabled. This is important for the loop. **********************************************************************************/ DECLARE @JobsEnabled TABLE ([Id] INT PRIMARY KEY IDENTITY(1, 1), [job_id] UNIQUEIDENTIFIER, [schedule_id] BIGINT, [enabled] BIT ); /***************************************** Insert schedules that we need to disable. *****************************************/ INSERT INTO @JobsEnabled ( [job_id], [schedule_id], [enabled] ) SELECT [job_id], [schedule_id], [enabled] FROM [dbo].[JobsEnabledTracker]; /******************************** Holds the job id and schedule id ********************************/ DECLARE @jobid UNIQUEIDENTIFIER; DECLARE @scheduleid BIGINT; /*********************************** Holds the ID of the row in the loop ***********************************/ DECLARE @ID INT= 0; /********************** Check if records exist **********************/ IF EXISTS ( SELECT [Id] FROM @JobsEnabled ) BEGIN PRINT 'Loop mode, jobs found enabled.'; /********** Begin loop **********/ WHILE(1 = 1) BEGIN /*************************************** Grab jobid, scheduleid, and id of rows. ***************************************/ SELECT @jobid = ( SELECT TOP 1 [job_id] FROM @JobsEnabled ORDER BY [job_id] ); SELECT @scheduleid = ( SELECT TOP 1 [schedule_id] FROM @JobsEnabled ORDER BY [job_id] ); SELECT @ID = ( SELECT TOP 1 [Id] FROM @JobsEnabled ORDER BY [job_id] ); /************************************ Re-enable schedule associated to job ************************************/ PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255)); EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 0; /********************* Removes row from loop *********************/ DELETE FROM @JobsEnabled WHERE [Id] = @ID; UPDATE [dbo].[JobsEnabledTracker] SET [enabled] = 0 WHERE [job_id] = @jobid AND [schedule_id] = @scheduleid; /**************************** No more rows, stops deleting ****************************/ IF ( SELECT COUNT(*) FROM @JobsEnabled ) <= 0 BEGIN BREAK END; /******** End Loop ********/ END; PRINT 'Exiting loop, disabling schedules paired to jobs complete.'; /********** End elseif **********/ END; ELSE BEGIN PRINT 'All done'; END; END; ELSE BEGIN PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.'; END;
Script Two – Enabling Schedules
USE MSDB; /******************************************************************************* Check for history table. This physical table tells us what jobs we are going to enable the scheduler for. *******************************************************************************/ IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL BEGIN IF EXISTS ( SELECT 1 FROM [dbo].[JobsEnabledTracker] WHERE [enabled] = 0 ) BEGIN PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.'; /********************************************************************************** Table variable to hold schedules and jobs enabled. This is important for the loop. **********************************************************************************/ DECLARE @JobsEnabled TABLE ( [Id] int PRIMARY KEY IDENTITY(1, 1) , [job_id] uniqueidentifier , [schedule_id] bigint , [enabled] bit ); /******************************************************************************* Insert schedules that we had disabled that we need to go back in and re-enable. *******************************************************************************/ INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] ) SELECT [job_id], [schedule_id], [enabled] FROM [dbo].[JobsEnabledTracker]; /******************************** Holds the job id and schedule id ********************************/ DECLARE @jobid uniqueidentifier; DECLARE @scheduleid bigint; /*********************************** Holds the ID of the row in the loop ***********************************/ DECLARE @ID int= 0; /********************** Check if records exist **********************/ IF EXISTS ( SELECT [Id] FROM @JobsEnabled ) BEGIN PRINT 'Loop mode, jobs found disabled.'; /********** Begin loop **********/ WHILE 1 = 1 BEGIN /*************************************** Grab jobid, scheduleid, and id of rows. ***************************************/ SELECT @jobid = ( SELECT TOP 1 [job_id] FROM @JobsEnabled ORDER BY [job_id] ); SELECT @scheduleid = ( SELECT TOP 1 [schedule_id] FROM @JobsEnabled ORDER BY [job_id] ); SELECT @ID = ( SELECT TOP 1 [Id] FROM @JobsEnabled ORDER BY [job_id] ); /*************************************** Re-enable schedule associated to job ***************************************/ PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255)); EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1; /********************* Removes row from loop *********************/ DELETE FROM @JobsEnabled WHERE [Id] = @ID; /*********************** Set job back to enabled ***********************/ UPDATE [dbo].[JobsEnabledTracker] SET [enabled] = 1 WHERE [job_id] = @jobid AND [schedule_id] = @scheduleid; /**************************** No more rows, stops deleting ****************************/ IF ( SELECT COUNT(*) FROM @JobsEnabled ) <= 0 BEGIN BREAK; END; /******** End Loop ********/ END; PRINT 'Exiting loop, enabling schedules paired to jobs complete.'; /********** End elseif **********/ END; ELSE BEGIN PRINT 'All done'; END; END; ELSE BEGIN PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.'; END; END; ELSE BEGIN PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.'; END;