How to stop the SQL Server Agent Job Scheduler with T-SQL?

Posted on

Question :

I have a job that I manually start in coordinated time periods that does a bunch of heavy lifting (mostly restoring backups and configuring for a different server)

There are several jobs scheduled to run with increments of minutes to days. I want to “turn off” the scheduler so nothing will run on schedule until my manual job is done. I realize that missed jobs will not run again until their next scheduled time, after the scheduler is restarted.

  • First Step: stop scheduler
  • Several Heavy lifting steps: Do the hard work
  • Last Step: start scheduler

I found An Oracle command but I am 100% Microsoft SQL Server

I know that if I Turn off the SQL agent, jobs will not run but that is not what I am looking for.

The jobs that exist for today, are not expected to be the same jobs next time. Disabling jobs with EXEC dbo.sp_update_job is not a viable solution.

Additional information: There may also be jobs in place that are intentionally disabled for some reason, that do want to be re-enabled. Stopping the scheduler seems like the best choice.

Answer :

I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)

SELECT * FROM 
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
   ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1

That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.

You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.

If you would like an example of a loop, you can see an example I have created in the past on stack overflow.

I have gone back and used the suggestions to create a complete code solution. Please see below, there are two scripts in total. The first will disable your jobs and the second will re-enable them. TEST THIS IN A TEST ENVIRONMENT BEFORE PUTTING IT IN PRODUCTION. Source for completeness.

Script one:

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

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;

sp_stop_job ?

Link to documentation: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

Leave a Reply

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