Disabling All Agent Jobs During a Freeze and Restoring Their State

Disabling all agent jobs for a freeze is easy. Getting back to where you started is the hard part. Save each job’s enabled flag first. Afterward, restore those flags instead of enabling everything.

Why “enable all” is a trap

Picture a Friday night migration. The plan says to disable every Agent job so nothing runs during the change. On Sunday evening the plan says to enable them all. Everyone nods.

On Monday, an old report job runs at 3 AM and fills someone’s inbox. That job had been switched off on purpose a long time ago. The freeze plan forgot that some jobs were already disabled, and “enable all” quietly woke it up.

The fix is boring and reliable. Write down the state before you touch anything, then put back exactly what you wrote down.

Set up two demo jobs

The demo creates two jobs in msdb. They are server-level objects, so the last block deletes them. DemoFreezeLoad is enabled. DemoFreezeReport is disabled, like that old report. Both run every 10 seconds and each run takes 15 seconds, so you can watch them.

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoFreezeLoad')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeLoad';
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoFreezeReport')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeReport';
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoFreezeLate')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeLate';

EXEC msdb.dbo.sp_add_job @job_name = N'DemoFreezeLoad', @enabled = 1;
EXEC msdb.dbo.sp_add_job @job_name = N'DemoFreezeReport', @enabled = 0;

EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoFreezeLoad', @step_name = N'Work',
     @subsystem = N'TSQL', @command = N'WAITFOR DELAY ''00:00:15'';';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoFreezeReport', @step_name = N'Work',
     @subsystem = N'TSQL', @command = N'WAITFOR DELAY ''00:00:15'';';

EXEC msdb.dbo.sp_add_jobschedule @job_name = N'DemoFreezeLoad', @name = N'Every 10 seconds',
     @freq_type = 4, @freq_interval = 1, @freq_subday_type = 2, @freq_subday_interval = 10;
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'DemoFreezeReport', @name = N'Every 10 seconds',
     @freq_type = 4, @freq_interval = 1, @freq_subday_type = 2, @freq_subday_interval = 10;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoFreezeLoad', @server_name = N'(local)';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoFreezeReport', @server_name = N'(local)';

To keep an eye on them I use a small view. It shows each job’s enabled flag, whether it is running right now, and how many runs have finished. It only looks at jobs named DemoFreeze, which keeps it away from your real jobs. Drop that filter for a real freeze.

CREATE OR ALTER VIEW dbo.FreezeCheck
AS
SELECT j.name, j.enabled,
       CASE WHEN a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL
            THEN N'running' ELSE N'idle' END AS state,
       (SELECT COUNT(*) FROM msdb.dbo.sysjobhistory AS h
        WHERE h.job_id = j.job_id AND h.step_id = 0) AS runs_finished
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobactivity AS a
       ON a.job_id = j.job_id
      AND a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
WHERE j.name LIKE N'DemoFreeze%';

Look at running jobs first

Disabling a job stops its next run. It does not stop a run that has already started. So before the freeze, check what is running right now. The loop below only waits until the demo job starts, for up to a minute.

DECLARE @Seconds int = 0;
WHILE @Seconds < 60 AND NOT EXISTS
      (SELECT 1 FROM dbo.FreezeCheck WHERE name = N'DemoFreezeLoad' AND state = N'running')
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @Seconds += 1;
END;

SELECT name, enabled, state, runs_finished
FROM dbo.FreezeCheck
ORDER BY name;

DemoFreezeLoad is enabled and running. DemoFreezeReport is disabled and idle, with no finished runs. On a real server, wait for such jobs to finish, or stop them on purpose.

Save the starting state

Now the snapshot. A temp table would vanish if your connection dropped in the middle of the freeze, so use a real table. I give each freeze a number and a capture time. The table lives in your utility database, not in msdb.

The result shows DemoFreezeLoad with was_enabled 1 and DemoFreezeReport with 0. That second row is exactly the one “enable all” would have ruined.

DROP TABLE IF EXISTS dbo.JobFreezeState;

CREATE TABLE dbo.JobFreezeState
(
    freeze_id   int              NOT NULL,
    job_id      uniqueidentifier NOT NULL,
    job_name    sysname          NOT NULL,
    was_enabled bit              NOT NULL,
    captured_at datetime2        NOT NULL DEFAULT SYSDATETIME(),
    PRIMARY KEY (freeze_id, job_id)
);

-- Remove the WHERE line to freeze every job on the server.
INSERT dbo.JobFreezeState (freeze_id, job_id, job_name, was_enabled)
SELECT 1, job_id, name, enabled
FROM msdb.dbo.sysjobs
WHERE name LIKE N'DemoFreeze%';

SELECT job_name, was_enabled
FROM dbo.JobFreezeState
WHERE freeze_id = 1
ORDER BY job_name;

Start the freeze

Disable only the jobs that were enabled. Use sp_update_job, never an UPDATE against the msdb tables. Look at the result closely: DemoFreezeLoad is now disabled, yet it still says running. The freeze did not stop it.

DECLARE @JobId uniqueidentifier;

DECLARE JobList CURSOR LOCAL FAST_FORWARD FOR
    SELECT job_id FROM dbo.JobFreezeState WHERE freeze_id = 1 AND was_enabled = 1;

OPEN JobList;
FETCH NEXT FROM JobList INTO @JobId;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job @job_id = @JobId, @enabled = 0;
    FETCH NEXT FROM JobList INTO @JobId;
END;
CLOSE JobList;

SELECT name, enabled, state, runs_finished
FROM dbo.FreezeCheck
ORDER BY name;

Now imagine a teammate creates a new job in the middle of the freeze. Jobs are enabled when created, so this one is live.

EXEC msdb.dbo.sp_add_job @job_name = N'DemoFreezeLate', @enabled = 1;

Watch the freeze hold

First wait for the run that was already going. The first result shows it finished and the job is idle. Then wait 30 seconds, three schedule ticks. The second result has the same runs_finished. A frozen job stays quiet.

DECLARE @Seconds int = 0;
WHILE @Seconds < 60 AND EXISTS
      (SELECT 1 FROM dbo.FreezeCheck WHERE name = N'DemoFreezeLoad' AND state = N'running')
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @Seconds += 1;
END;

SELECT name, enabled, state, runs_finished FROM dbo.FreezeCheck ORDER BY name;

WAITFOR DELAY '00:00:30';

SELECT name, enabled, state, runs_finished FROM dbo.FreezeCheck ORDER BY name;

Restore the saved flags and report the surprises

Restoring means putting back each saved flag. It does not mean enabling everything. Here only DemoFreezeLoad goes back to enabled. Then the loop waits for its next scheduled run. The result shows DemoFreezeLoad running again, while DemoFreezeReport is still disabled with zero runs.

DECLARE @JobId uniqueidentifier;

DECLARE JobList CURSOR LOCAL FAST_FORWARD FOR
    SELECT job_id FROM dbo.JobFreezeState WHERE freeze_id = 1 AND was_enabled = 1;

OPEN JobList;
FETCH NEXT FROM JobList INTO @JobId;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job @job_id = @JobId, @enabled = 1;
    FETCH NEXT FROM JobList INTO @JobId;
END;
CLOSE JobList;

DECLARE @Seconds int = 0;
WHILE @Seconds < 60 AND NOT EXISTS
      (SELECT 1 FROM dbo.FreezeCheck WHERE name = N'DemoFreezeLoad' AND state = N'running')
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @Seconds += 1;
END;

SELECT name, enabled, state, runs_finished FROM dbo.FreezeCheck ORDER BY name;

Two checks follow. The first lists any saved job whose flag differs from the snapshot, or that has vanished. It returns no rows when all is well. The second lists jobs that were not in the snapshot. It finds DemoFreezeLate. I report such jobs instead of guessing what they should be. That is a decision for a person.

SELECT s.job_name, s.was_enabled, j.enabled AS enabled_now
FROM dbo.JobFreezeState AS s
LEFT JOIN msdb.dbo.sysjobs AS j ON j.job_id = s.job_id
WHERE s.freeze_id = 1 AND (j.job_id IS NULL OR j.enabled <> s.was_enabled);

SELECT j.name AS job_added_during_freeze, j.enabled
FROM msdb.dbo.sysjobs AS j
WHERE j.name LIKE N'DemoFreeze%'
  AND NOT EXISTS (SELECT 1 FROM dbo.JobFreezeState AS s WHERE s.freeze_id = 1 AND s.job_id = j.job_id);

Keep the snapshot table until someone has looked at the jobs and agreed everything is right. It costs almost nothing. The last block removes the demo jobs, the view and the table.

EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeLoad';
EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeReport';
EXEC msdb.dbo.sp_delete_job @job_name = N'DemoFreezeLate';
DROP VIEW IF EXISTS dbo.FreezeCheck;
DROP TABLE IF EXISTS dbo.JobFreezeState;

Before your next freeze, write the starting state down, and then trust the paper, not your memory.

A freeze is not an enable-all command, it is a temporary change you can undo exactly.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.