DBA Scripts: Enable or Disable All SQL Agent Jobs

Turning every SQL Agent job off is easy. Turning the right ones back on is where it goes wrong. Somebody disables the lot for a migration window, the window runs long, and three days later a backup job is still off because nobody wrote down which ones had been enabled in the first place.

This is the script I use instead. It snapshots the enabled flag for every job before it touches anything, and it refuses to disable anything until it has. Everything below was run against a SQL Server 2025 instance with 26 real Agent jobs, 20 of them enabled.

---

When to Run This Script

Any window where jobs firing would be unhelpful or actively harmful:

• A migration or cutover, where an index maintenance job starting mid-move is the last thing you need
• Patching, so nothing kicks off between the restart and your sanity checks
• Restoring a database that a collector job writes into
• Handing an instance to an application team for testing, where you want it quiet but reversible

It is not a monitoring script and it is not scheduled. You run it deliberately, in a window, and you run Restore at the end of that window.

---

The Script

One script, four actions, set at the top of the parameter block. Report is the default, so running it by accident tells you things rather than changing them.

/*
Script Name : Set-AgentJobState
Category    : maintenance
Purpose     : Save, disable and restore SQL Agent job state as one operation, so a maintenance
              window can quiet an instance and then put it back EXACTLY as it was.
              @Action = N'Report'   lists every job with the change that would be made. Default.
              @Action = N'Save'     snapshots each job's enabled flag into a state table.
              @Action = N'Disable'  disables enabled jobs, honouring the exclusion lists.
              @Action = N'Restore'  re-applies the latest snapshot and proves the result matches.
Author      : Peter Whyte (https://sqldba.blog)
Requires    : SQLAgentOperatorRole or sysadmin. Save also needs CREATE TABLE in @StateDatabase.
Notes       : Disabling is not stopping. A job already running keeps running after it is disabled;
              set @StopRunning = 1 to stop in-flight jobs as well, and understand that stopping a
              job mid-step leaves whatever it was doing half done.
              Restore only touches jobs whose current state DISAGREES with the snapshot, so it
              cannot re-enable something you disabled deliberately after taking the snapshot.
              Jobs created after the snapshot are not in it. Restore leaves them alone and Report
              lists them, because guessing at their intended state is not a rollback.
              Disable refuses to run when no snapshot exists. That is the whole safety property:
              never turn everything off without a recorded way back.
*/
-- WARNING: Disable turns off EVERY Agent job that is not excluded — run Save first
-- SAFE:CreatesObjects
-- IMPACT:High
SET NOCOUNT ON;

-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @Action            nvarchar(10)  = N'Report';        -- Report | Save | Disable | Restore
DECLARE @StateDatabase     sysname       = N'DBA_Admin';     -- a utility database, NOT tempdb
DECLARE @ExcludeCategories nvarchar(max) = N'Log Shipping,REPL-Distribution,REPL-LogReader,REPL-Merge,REPL-Snapshot';
DECLARE @ExcludeJobs       nvarchar(max) = N'';              -- comma separated exact job names
DECLARE @StopRunning       bit           = 0;                -- Disable only: stop in-flight jobs
-- ─────────────────────────────────────────────────────────────────────────────

IF @Action NOT IN (N'Report', N'Save', N'Disable', N'Restore')
BEGIN
    RAISERROR('@Action must be Report, Save, Disable or Restore.', 16, 1);
    RETURN;
END

IF DB_ID(@StateDatabase) IS NULL
BEGIN
    RAISERROR('@StateDatabase %s does not exist on this instance.', 16, 1, @StateDatabase);
    RETURN;
END

IF @StateDatabase IN (N'tempdb')
BEGIN
    RAISERROR('@StateDatabase must not be tempdb - the snapshot would not survive a restart.', 16, 1);
    RETURN;
END

DECLARE @sql   nvarchar(max);
DECLARE @db    nvarchar(300) = QUOTENAME(@StateDatabase);
DECLARE @rows  int;

-- Exclusions, resolved once into a temp table so every branch agrees on the same set.
IF OBJECT_ID('tempdb..#excluded') IS NOT NULL DROP TABLE #excluded;
CREATE TABLE #excluded (job_id uniqueidentifier PRIMARY KEY, name sysname, reason nvarchar(60));

INSERT INTO #excluded (job_id, name, reason)
SELECT j.job_id, j.name, N'category ' + c.name
FROM   msdb.dbo.sysjobs j
JOIN   msdb.dbo.syscategories c ON c.category_id = j.category_id
WHERE  LTRIM(RTRIM(c.name)) IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ExcludeCategories, ',')
                                WHERE LTRIM(RTRIM(value)) <> N'');

INSERT INTO #excluded (job_id, name, reason)
SELECT j.job_id, j.name, N'named in @ExcludeJobs'
FROM   msdb.dbo.sysjobs j
WHERE  LTRIM(RTRIM(j.name)) IN (SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@ExcludeJobs, ',')
                                WHERE LTRIM(RTRIM(value)) <> N'')
AND    NOT EXISTS (SELECT 1 FROM #excluded e WHERE e.job_id = j.job_id);

-- The latest snapshot, lifted out of @StateDatabase into a temp table so the rest of the script
-- is plain readable T-SQL rather than one long dynamic string.
IF OBJECT_ID('tempdb..#snapshot') IS NOT NULL DROP TABLE #snapshot;
CREATE TABLE #snapshot (job_id uniqueidentifier PRIMARY KEY, name sysname, enabled tinyint,
                        CapturedAt datetime2(0));

SET @sql = N'
IF OBJECT_ID(''' + @db + N'.dbo.AgentJobState'') IS NOT NULL
    SELECT job_id, name, enabled, CapturedAt
    FROM   ' + @db + N'.dbo.AgentJobState
    WHERE  CapturedAt = (SELECT MAX(CapturedAt) FROM ' + @db + N'.dbo.AgentJobState);';
INSERT INTO #snapshot (job_id, name, enabled, CapturedAt) EXEC sp_executesql @sql;

DECLARE @snapAt datetime2(0) = (SELECT MAX(CapturedAt) FROM #snapshot);
DECLARE @snapN  int          = (SELECT COUNT(*) FROM #snapshot);

-- ══ Report ═══════════════════════════════════════════════════════════════════
IF @Action = N'Report'
BEGIN
    IF @snapAt IS NULL
        PRINT 'No snapshot found in ' + @StateDatabase + '.dbo.AgentJobState. Run @Action = Save first.';
    ELSE
        PRINT 'Latest snapshot: ' + CONVERT(varchar(19), @snapAt, 120)
              + ' holding ' + CAST(@snapN AS varchar(10)) + ' job(s).';

    SELECT j.name,
           c.name                                      AS category,
           j.enabled                                   AS enabled_now,
           s.enabled                                   AS enabled_in_snapshot,
           e.reason                                    AS excluded_because,
           CASE
               WHEN e.job_id IS NOT NULL              THEN N'excluded, left alone'
               WHEN s.job_id IS NULL                  THEN N'not in snapshot - Restore will not touch it'
               WHEN j.enabled = 1                     THEN N'Disable would turn this off'
               ELSE                                        N'already disabled'
           END                                         AS on_disable,
           CASE
               WHEN s.job_id IS NULL                  THEN N'no snapshot row'
               WHEN s.enabled = j.enabled             THEN N'already matches'
               ELSE N'Restore would set enabled = ' + CAST(s.enabled AS varchar(1))
           END                                         AS on_restore
    FROM   msdb.dbo.sysjobs j
    JOIN   msdb.dbo.syscategories c ON c.category_id = j.category_id
    LEFT   JOIN #snapshot s ON s.job_id = j.job_id
    LEFT   JOIN #excluded e ON e.job_id = j.job_id
    ORDER  BY c.name, j.name;

    SELECT COUNT(*)                                                AS jobs_total,
           SUM(CASE WHEN j.enabled = 1 THEN 1 ELSE 0 END)          AS enabled_now,
           (SELECT COUNT(*) FROM #excluded)                        AS excluded,
           @snapN                                                  AS in_snapshot,
           SUM(CASE WHEN s.job_id IS NULL THEN 1 ELSE 0 END)       AS added_since_snapshot
    FROM   msdb.dbo.sysjobs j
    LEFT   JOIN #snapshot s ON s.job_id = j.job_id;

    -- The statements Disable would run, and the ones that would undo it. Report is a dry run,
    -- so this is the script you review before committing to anything - or save and run by hand
    -- if you would rather drive it yourself than let the tool do it.
    SELECT j.name                                                  AS job,
           N'EXEC msdb.dbo.sp_update_job @job_name = N'
             + QUOTENAME(j.name, '''') + N', @enabled = 0;'        AS disable_statement,
           N'EXEC msdb.dbo.sp_update_job @job_name = N'
             + QUOTENAME(j.name, '''') + N', @enabled = 1;'        AS enable_statement
    FROM   msdb.dbo.sysjobs j
    JOIN   msdb.dbo.syscategories c ON c.category_id = j.category_id
    WHERE  j.enabled = 1
    AND    NOT EXISTS (SELECT 1 FROM #excluded e WHERE e.job_id = j.job_id)
    ORDER  BY c.name, j.name;
    RETURN;
END

-- ══ Save ═════════════════════════════════════════════════════════════════════
IF @Action = N'Save'
BEGIN
    SET @sql = N'
    IF OBJECT_ID(''' + @db + N'.dbo.AgentJobState'') IS NULL
        CREATE TABLE ' + @db + N'.dbo.AgentJobState (
            CapturedAt datetime2(0)      NOT NULL CONSTRAINT DF_AgentJobState_CapturedAt DEFAULT SYSDATETIME(),
            job_id     uniqueidentifier  NOT NULL,
            name       sysname           NOT NULL,
            enabled    tinyint           NOT NULL,
            CONSTRAINT PK_AgentJobState PRIMARY KEY (CapturedAt, job_id)
        );';
    EXEC sp_executesql @sql;

    -- One CapturedAt for the whole snapshot. SYSDATETIME() per row would split a single save
    -- across two timestamps and Restore reads MAX(CapturedAt), so it would then restore a
    -- fraction of the instance and report success.
    DECLARE @now datetime2(0) = SYSDATETIME();
    SET @sql = N'
    INSERT INTO ' + @db + N'.dbo.AgentJobState (CapturedAt, job_id, name, enabled)
    SELECT @now, job_id, name, enabled FROM msdb.dbo.sysjobs;';
    EXEC sp_executesql @sql, N'@now datetime2(0)', @now = @now;
    SET @rows = @@ROWCOUNT;

    PRINT 'Saved ' + CAST(@rows AS varchar(10)) + ' job(s) at ' + CONVERT(varchar(19), @now, 120)
          + ' into ' + @StateDatabase + '.dbo.AgentJobState.';

    SELECT @rows                                                     AS jobs_saved,
           (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE enabled = 1) AS were_enabled,
           @now                                                      AS captured_at;
    RETURN;
END

-- ══ Disable ══════════════════════════════════════════════════════════════════
IF @Action = N'Disable'
BEGIN
    -- The safety property. Without a snapshot there is no way back, and "I will remember which
    -- ones were on" is not a rollback plan.
    IF @snapAt IS NULL
    BEGIN
        RAISERROR('No snapshot in %s.dbo.AgentJobState. Run @Action = Save before disabling.',
                  16, 1, @StateDatabase);
        RETURN;
    END

    DECLARE @job sysname, @disabled int = 0;

    -- Record WHAT is about to be turned off, before turning it off. Once the loop has run,
    -- msdb no longer knows which jobs were enabled a moment ago, and a count is not a record.
    -- The DBA gets the list, and an undo statement per job that works even if the state table
    -- is lost, the window runs into the next shift, or somebody restores over the database.
    IF OBJECT_ID('tempdb..#changed') IS NOT NULL DROP TABLE #changed;
    CREATE TABLE #changed (name sysname, category sysname);
    INSERT INTO #changed (name, category)
    SELECT j.name, c.name
    FROM   msdb.dbo.sysjobs j
    JOIN   msdb.dbo.syscategories c ON c.category_id = j.category_id
    WHERE  j.enabled = 1
    AND    NOT EXISTS (SELECT 1 FROM #excluded e WHERE e.job_id = j.job_id);

    DECLARE job_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT j.name
        FROM   msdb.dbo.sysjobs j
        WHERE  j.enabled = 1
        AND    NOT EXISTS (SELECT 1 FROM #excluded e WHERE e.job_id = j.job_id);

    OPEN job_cur;
    FETCH NEXT FROM job_cur INTO @job;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC msdb.dbo.sp_update_job @job_name = @job, @enabled = 0;
        SET @disabled += 1;
        FETCH NEXT FROM job_cur INTO @job;
    END
    CLOSE job_cur;
    DEALLOCATE job_cur;

    DECLARE @stopped int = 0;
    IF @StopRunning = 1
    BEGIN
        DECLARE stop_cur CURSOR LOCAL FAST_FORWARD FOR
            SELECT j.name
            FROM   msdb.dbo.sysjobactivity ja
            JOIN   msdb.dbo.sysjobs j ON j.job_id = ja.job_id
            WHERE  ja.run_requested_date IS NOT NULL
            AND    ja.stop_execution_date IS NULL
            AND    NOT EXISTS (SELECT 1 FROM #excluded e WHERE e.job_id = j.job_id);
        OPEN stop_cur;
        FETCH NEXT FROM stop_cur INTO @job;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC msdb.dbo.sp_stop_job @job_name = @job;
            SET @stopped += 1;
            FETCH NEXT FROM stop_cur INTO @job;
        END
        CLOSE stop_cur;
        DEALLOCATE stop_cur;
    END

    DECLARE @exCount int = (SELECT COUNT(*) FROM #excluded);
    PRINT 'Disabled ' + CAST(@disabled AS varchar(10)) + ' job(s), stopped '
          + CAST(@stopped AS varchar(10)) + ', excluded '
          + CAST(@exCount AS varchar(10)) + '.';

    SELECT @disabled                                                    AS jobs_disabled,
           @stopped                                                     AS jobs_stopped,
           (SELECT COUNT(*) FROM #excluded)                             AS jobs_excluded,
           (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE enabled = 1)    AS still_enabled;

    -- EXACTLY what was turned off, and the statement that puts each one back. Copy the last
    -- column out and you have a rollback script that depends on nothing but msdb - useful when
    -- the window runs into somebody else's shift, or the state database is itself being restored.
    SELECT c.name                                              AS job_disabled,
           c.category,
           N'EXEC msdb.dbo.sp_update_job @job_name = N'
             + QUOTENAME(c.name, '''') + N', @enabled = 1;'    AS undo_statement
    FROM   #changed c
    ORDER  BY c.category, c.name;

    SELECT name AS job_left_alone, reason FROM #excluded ORDER BY name;
    RETURN;
END

-- ══ Restore ══════════════════════════════════════════════════════════════════
IF @Action = N'Restore'
BEGIN
    IF @snapAt IS NULL
    BEGIN
        RAISERROR('No snapshot in %s.dbo.AgentJobState to restore from.', 16, 1, @StateDatabase);
        RETURN;
    END

    DECLARE @rjob sysname, @was tinyint, @changed int = 0;

    -- Same reasoning as Disable: capture the list before the loop changes it, so the DBA gets a
    -- record of what moved rather than a number.
    IF OBJECT_ID('tempdb..#restored') IS NOT NULL DROP TABLE #restored;
    CREATE TABLE #restored (name sysname, was tinyint, is_now tinyint);
    INSERT INTO #restored (name, was, is_now)
    SELECT s.name, j.enabled, s.enabled
    FROM   #snapshot s
    JOIN   msdb.dbo.sysjobs j ON j.job_id = s.job_id
    WHERE  j.enabled <> s.enabled;

    -- Only where the CURRENT state disagrees with the snapshot. Re-applying every row would
    -- undo anything you changed on purpose after taking it, and would report those as restored.
    DECLARE res_cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT s.name, s.enabled
        FROM   #snapshot s
        JOIN   msdb.dbo.sysjobs j ON j.job_id = s.job_id
        WHERE  j.enabled <> s.enabled;

    OPEN res_cur;
    FETCH NEXT FROM res_cur INTO @rjob, @was;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC msdb.dbo.sp_update_job @job_name = @rjob, @enabled = @was;
        SET @changed += 1;
        FETCH NEXT FROM res_cur INTO @rjob, @was;
    END
    CLOSE res_cur;
    DEALLOCATE res_cur;

    PRINT 'Restored ' + CAST(@changed AS varchar(10)) + ' job(s) to the snapshot taken '
          + CONVERT(varchar(19), @snapAt, 120) + '.';

    -- What actually moved, and in which direction.
    SELECT name        AS job_restored,
           was         AS state_before_restore,
           is_now      AS state_after_restore
    FROM   #restored
    ORDER  BY name;

    -- The proof. Anything returned here is a job that did NOT come back as recorded.
    SELECT s.name, s.enabled AS was, j.enabled AS [now]
    FROM   #snapshot s
    JOIN   msdb.dbo.sysjobs j ON j.job_id = s.job_id
    WHERE  j.enabled <> s.enabled;

    -- Jobs created since the snapshot. Not an error, but they are yours to decide on.
    SELECT j.name AS created_since_snapshot, j.enabled
    FROM   msdb.dbo.sysjobs j
    WHERE  NOT EXISTS (SELECT 1 FROM #snapshot s WHERE s.job_id = j.job_id)
    ORDER  BY j.name;

    SELECT @changed AS jobs_changed, @snapAt AS restored_from, @snapN AS jobs_in_snapshot;
    RETURN;
END

Three things in it are worth calling out, because they are the parts that a hand-rolled version usually gets wrong.

Save writes one timestamp for the whole snapshot. If you let SYSDATETIME() evaluate per row, a single save can straddle two timestamps. Restore reads MAX(CapturedAt), so it would then restore a fraction of the instance and report success.

Restore only touches jobs whose current state disagrees with the snapshot. Re-applying every saved row would undo anything you changed deliberately after taking it, and would report those as restored. On the test instance, running Restore a second time reported 0 jobs changed, which is what idempotent should look like.

Jobs created after the snapshot are left alone. They are not in it, so there is nothing to restore them to. Restore lists them separately rather than guessing, because guessing at their intended state is not a rollback.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# See what would change, without changing anything:
.\run.ps1 Set-AgentJobState -Action Report -StateDatabase DBA_Admin

# Snapshot the current state, then disable:
.\run.ps1 Set-AgentJobState -Action Save    -StateDatabase DBA_Admin
.\run.ps1 Set-AgentJobState -Action Disable -StateDatabase DBA_Admin -Force

# Leave some jobs running:
.\run.ps1 Set-AgentJobState -Action Disable -StateDatabase DBA_Admin -Force `
          -ExcludeJobs 'DBA - Collect Deadlocks,DBA - Collect Perfmon'

# At the end of the window, put everything back:
.\run.ps1 Set-AgentJobState -Action Restore -StateDatabase DBA_Admin

-Force is required for Disable and nothing else. Without it the wrapper refuses and tells you to run Report first.

This script lives in the repo at:

• sql/maintenance/Set-AgentJobState.sql
• powershell/wrappers/maintenance/Set-AgentJobState.ps1

The .sql file runs on its own in SSMS as well. Edit the parameter block at the top and run it. The wrapper substitutes those same parameters before executing, so the two paths do the same thing.

---

Example Output

Three actions, in the order you would run them. Every one returns the list, not just a count. That matters at 2am: 18 jobs disabled tells you nothing you can act on, and once the loop has run, msdb no longer knows which jobs were enabled a moment earlier.

Report, before you change anything

A row per job, with a column for what each action would do:

Alongside it comes a second result set, one row per job it would turn off, carrying a ready-made disable_statement and the matching enable_statement: EXEC msdb.dbo.sp_update_job @job_name = N'DBA - Index Maintenance', @enabled = 0; and the same line with @enabled = 1.

If you would rather drive it yourself, that is already a complete script. Copy the disable_statement column out of the results grid, save it, run it in your window. Nothing here obliges you to let the tool make the change.

Disable, and the way back

It reports the totals, then names every job it turned off with the statement that puts each one back:

Disabled 18 job(s), stopped 0, excluded 2.

jobs_disabled jobs_stopped jobs_excluded still_enabled
------------- ------------ ------------- -------------
           18            0             2             2

job_disabled                  category               undo_statement
----------------------------  ---------------------  ----------------------------------------------
DBA - Index Maintenance       Database Maintenance   EXEC msdb.dbo.sp_update_job @job_name = N'...
DBA - Statistics Update       Database Maintenance   EXEC msdb.dbo.sp_update_job @job_name = N'...
DBA - Collect Blocking        DBA Collectors         EXEC msdb.dbo.sp_update_job @job_name = N'...

That undo column depends on nothing but msdb. Not the state table, not this script. Save it somewhere off the server and the window can run into somebody else’s shift, or the state database can itself be mid-restore, and the way back still exists. It was tested exactly that way: the 20 emitted statements were run on their own, without Restore, and put every job back as it had been.

Restore, and its proof

What moved and in which direction, then a query that returns any job which did not come back as recorded. That second result set should be empty. A third lists jobs created since the snapshot, which is information rather than an error.

---

Understanding the Results

The number to look at on Disable is still_enabled. It should equal the number you deliberately excluded. Anything higher means a job slipped through, which in practice means it was created between your Report and your Disable.

On Restore, the script runs its own proof: a query listing any job whose current state does not match the snapshot. That result set should be empty. If it returns rows, those jobs did not come back as recorded and are worth looking at by hand before you close the window.

A second result set lists jobs created since the snapshot. That is information, not an error, but on a shared instance it is often the first sign that somebody else has been working while your window was open.

---

Disabling Is Not Stopping

This catches people out, and it is worth being clear about. Disabling a job does not stop it if it is already running. Disabled means the schedule will not start it again. A job that was already executing carries on to the end.

So if your window depends on nothing being in flight, disabling alone is not enough. Check what is actually executing:

SELECT j.name, ja.run_requested_date
FROM   msdb.dbo.sysjobactivity ja
JOIN   msdb.dbo.sysjobs j ON j.job_id = ja.job_id
WHERE  ja.run_requested_date IS NOT NULL
AND    ja.stop_execution_date IS NULL;

The script will stop them for you with -StopRunning, but understand what you are asking for. Stopping a job mid-step leaves whatever it was doing half done: an index rebuild abandoned partway, a backup file written but incomplete. Disable first, then decide about the ones still running, one at a time.

---

Leaving Some Jobs Alone

Log shipping and replication jobs are usually the ones you do not want to touch, because turning them off quietly builds a backlog that somebody else discovers later. Those categories are excluded by default:

-- see what categories exist on this instance first
SELECT j.name, c.name AS category, j.enabled
FROM   msdb.dbo.sysjobs j
JOIN   msdb.dbo.syscategories c ON c.category_id = j.category_id
ORDER  BY c.name, j.name;

Exclusions work by category or by exact job name, and Report shows you which rule caught each one in its excluded_because column, so you are never guessing at why something stayed on.

---

Related Scripts

• Get Maintenance Job Status, the read-only view of what your jobs have been doing
• Generate Maintenance Jobs, for building the job framework this script switches on and off
• SET Options Have Incorrect Settings (Error 1934), the classic reason an Agent job fails while the same query works in SSMS
• Silent Failures, on jobs that stopped working without telling anyone

---

Frequently Asked Questions

---

Summary

Disabling every Agent job is one line of thinking. Putting them back exactly as they were is the part that needs a record, and “they were mostly on” is not a record. Snapshot first, disable with sp_update_job rather than by writing to the system table, exclude log shipping and replication, and remember that a disabled job that was already running is still running.

Then, at the end of the window, restore from what you saved and check that the proof query comes back empty.