SQL Server Agent Job Scheduler – How to Stop with T-SQL

jobsscheduled-taskssql serversql server 2014

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.

Best 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;

Related Solutions

Sql-server – How does SQL Server store the information in Job Schedule Properties

Every 20 minutes (this seems to NOT be configurable), the 'next run' information in sysjobschedules is updated by looking at the information you can see in sysschedules.

You know how when you define a job you have to go through a slightly convoluted process of rather than just saying 'run this job every monday', instead defining a schedule that says that, and associating the job with the schedule? This is why - they have independent existences.

SQL Server – How to Find Next Scheduled Job Run Time in Real Time

the actual problem is well described and answered on the question below:

How and when does SQL Agent update next_run_date/next_run_time values?

one solution I found is to use the procedure below shows the next running time of a job, even if you have just created it.

just pass the jobName as parameter.

use master
go
CREATE PROCEDURE [dbo].[sp_ViewJobListing]
(
    @JobName VARCHAR(255)=NULL
)
AS
/*
   http://www.techrepublic.com/blog/the-enterprise-cloud/how-do-i-assign-permissions-to-users-to-see-sql-agent-jobs/
   sp_ViewJobListing @jobname = 'ADS Full MetaData Refresh'
*/
BEGIN
    IF OBJECT_ID('tempdb..#Results')>0
        DROP TABLE #Results
    CREATE TABLE #Results
    (
        job_id UNIQUEIDENTIFIER NOT NULL,
        last_run_date INT              NOT NULL,
        last_run_time INT              NOT NULL,
        next_run_date INT              NOT NULL,
        next_run_time INT              NOT NULL,
        next_run_schedule_id INT              NOT NULL,
        requested_to_run INT              NOT NULL,
        request_source INT              NOT NULL,
        request_source_id SYSNAME   COLLATE DATABASE_DEFAULT NULL,
        running  INT              NOT NULL,
        current_step INT              NOT NULL,
        current_retry_attempt  INT              NOT NULL,
        job_state  INT              NOT NULL
    )
    DECLARE @JobID VARCHAR(100)
    SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs
    INSERT INTO #Results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID
    SELECT
        s.Name,
        CASE WHEN s.enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled,
        CASE WHEN next_run_date > 0 THEN 'Yes' ELSE 'No' END AS Scheduled,
        sc.name AS Category,
        current_step AS CurrentExecutionStep,
        last_run_date,
        next_run_date,
        CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status,
        ISNULL((
            SELECT CASE WHEN run_status = 1 THEN 'Succeeded' WHEN run_status = 3 THEN 'Cancelled' WHEN run_status = 0 THEN 'Failed' WHEN run_status IS NULL THEN 'Unknown' END AS LastRunStatus
            FROM
                msdb..sysjobhistory sho
            WHERE
                sho.job_id = xp.job_id AND
                sho.instance_id =
                (
                    SELECT MAX(instance_id)
                    FROM msdb..sysjobhistory sj (NOLOCK)
                    WHERE sj.job_id = sho.job_id
                )
        ) ,'Unknown') AS LastRunStatus
    FROM     #Results xp
        INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id
        INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id
    WHERE
        s.Name = ISNULL(@JobName, s.Name)
    ORDER BY s.Name
    IF @JobName IS NOT NULL
    BEGIN
        CREATE TABLE #JobHistory
        (
            StepID INT,
            StepName SYSNAME,
            Message NVARCHAR(1024),
            RunStatus INT,
            RunDate INT,
            RunTime INT,
            RunDuration INT,
            operator_emailed NVARCHAR(20),
            operator_netsent NVARCHAR(20),
            operator_paged NVARCHAR(20)
        )
        INSERT INTO #JobHistory
        SELECT
            sjh.step_id,
            sjh.step_name,
            sjh.message,
            sjh.run_status,
            sjh.run_date,
            sjh.run_time,
            sjh.run_duration,
            operator_emailed = so1.name,
            operator_netsent = so2.name,
            operator_paged = so3.name
        FROM
            msdb.dbo.sysjobhistory sjh
            JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id
            LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON (sjh.operator_id_emailed = so1.id)
            LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON (sjh.operator_id_netsent = so2.id)
            LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON (sjh.operator_id_paged = so3.id),
            msdb.dbo.sysjobs                 sj
        WHERE
        sjj.Name = @JobName and
        (sj.job_id = sjh.job_id)
        SELECT
            StepID, StepName, Message, RunDate AS LastRunTime,
            CASE RunStatus
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry (step only)'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'In-progress message'
                WHEN 5 THEN 'Unknown'
            END AS RunStatus
        FROM #JobHistory
        ORDER BY LastRunTime DESC, StepID ASC
    END
END
GO
EXECUTE sp_ms_marksystemobject 'sp_ViewJobListing'

I have also found that when the next_run_date is not available from the jobschedule table (because it might take up to 20 min to update), I can get it from the table sysjobactivity, as you can see on the partial list from an example below:

,[Next_Run_Date] = 
        CASE [jobschedule].[next_run_date]
            WHEN 0 THEN (select top 1 next_scheduled_run_date from msdb.dbo.sysjobactivity WITh(NOLOCK) where job_id = [jobs].job_id order by run_requested_date desc )
            ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
                 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
        END

Best Answer

Related Solutions

Sql-server – How does SQL Server store the information in Job Schedule Properties

SQL Server – How to Find Next Scheduled Job Run Time in Real Time

Related Question