Disabling_Jobs_Schedules.sql

/*
<documentation>
  <summary>Disabling Jobs Shedules.</summary>
  <returns>None.</returns>
  <created>2018-12-11 by Jon Shaulis</created>
  <modified>2020-04-18 by Konstantin Taranov</modified>
  <version>1.0</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Disabling_Jobs_Schedules.sql.sql</sourceLink>
  <originalLink>https://jonshaulis.com/index.php/2018/12/11/how-to-stop-the-sql-scheduler-with-t-sql/</originalLink>
</documentation>
*/


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;