How to stop the SQL Scheduler with T-SQL

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;
Too much time coding and not enough time napping.

Leave a Reply

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