Who switched off the backup job? Alerting when SQL Server Agent jobs are enabled or disabled

A failed job raises an alert. A disabled job raises nothing: it just stops running. That's how backup or integrity-check jobs end up switched off for weeks after a maintenance window. You want to know, quickly, whenever a job is enabled or disabled, and by whom.

Three options, from least to most invasive:

Most teams use A for the alert and C for the evidence.

#Option A: polling job (recommended baseline)

USE msdb;
-- one-time: snapshot table in a DBA database (shown in msdb.dbo for brevity; prefer a DBA database)
CREATE TABLE dbo.dba_job_state (job_id uniqueidentifier PRIMARY KEY, job_name sysname, enabled tinyint, captured_at datetime2 DEFAULT SYSDATETIME());
INSERT dbo.dba_job_state (job_id, job_name, enabled) SELECT job_id, name, enabled FROM dbo.sysjobs;

-- job step, every 5 minutes: compare, notify, refresh
DECLARE @changes nvarchar(max) = N'';
SELECT @changes += CONCAT(j.name, N': ', CASE WHEN s.enabled IS NULL THEN N'NEW JOB (enabled=' + CAST(j.enabled AS nvarchar) + N')'
                                             WHEN j.enabled = 1 THEN N'ENABLED' ELSE N'DISABLED' END,
                          N' (modified ', CONVERT(nvarchar(19), j.date_modified, 120), N')', CHAR(13), CHAR(10))
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.dba_job_state s ON s.job_id = j.job_id
WHERE s.job_id IS NULL OR s.enabled <> j.enabled;

SELECT @changes += CONCAT(s.job_name, N': DELETED', CHAR(13), CHAR(10))
FROM msdb.dbo.dba_job_state s WHERE NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs j WHERE j.job_id = s.job_id);

IF @changes <> N''
BEGIN
    DECLARE @subject nvarchar(255) = CONCAT(N'SQL Agent job state changed on ', @@SERVERNAME);
    EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA', @recipients = N'dba-team@corp.example.com',
         @subject = @subject, @body = @changes;

    TRUNCATE TABLE msdb.dbo.dba_job_state;
    INSERT msdb.dbo.dba_job_state (job_id, job_name, enabled) SELECT job_id, name, enabled FROM msdb.dbo.sysjobs;
END

Schedule it every 5 minutes. It also reports new and deleted jobs, which is useful for the same reason.

Also watch schedules: a job can be "enabled" but have its only schedule disabled. Add the same comparison for msdb.dbo.sysschedules.enabled if that matters in your shop.

#Option B: trigger on msdb.dbo.sysjobs (instant)

USE msdb;
GO
CREATE OR ALTER TRIGGER dbo.trg_dba_job_enabled_changed ON dbo.sysjobs
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(enabled) RETURN;

    DECLARE @body nvarchar(max) = N'';
    SELECT @body += CONCAT(i.name, N' was ', CASE i.enabled WHEN 1 THEN N'ENABLED' ELSE N'DISABLED' END,
                           N' by ', ORIGINAL_LOGIN(), N' from ', HOST_NAME(), N' (', APP_NAME(), N') at ',
                           CONVERT(nvarchar(19), SYSDATETIME(), 120), CHAR(13), CHAR(10))
    FROM inserted i JOIN deleted d ON d.job_id = i.job_id
    WHERE i.enabled <> d.enabled;

    IF @body <> N''
    BEGIN
        DECLARE @subject nvarchar(255) = CONCAT(N'SQL Agent job enabled/disabled on ', @@SERVERNAME);
        EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA', @recipients = N'dba-team@corp.example.com',
             @subject = @subject, @body = @body;
    END
END;

Warning

Triggers on msdb system tables aren't a documented, supported customisation. Keep the trigger tiny and failure-proof: sp_send_dbmail only queues the message, so a mail outage won't block the change. Script it into source control, check it still exists after cumulative updates and upgrades, and remove it before you open a support case about Agent.

ORIGINAL_LOGIN() returns the login that connected, even if the change came through sp_update_job or the SSMS dialog.

#Option C: SQL Server Audit (who, when, exactly what)

Audit calls to the procedures that change jobs:

USE master;
CREATE SERVER AUDIT AgentJobChanges TO FILE (FILEPATH = N'L:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20) WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT AgentJobChanges WITH (STATE = ON);

USE msdb;
CREATE DATABASE AUDIT SPECIFICATION AgentJobChangeSpec FOR SERVER AUDIT AgentJobChanges
    ADD (EXECUTE ON OBJECT::dbo.sp_update_job      BY public),
    ADD (EXECUTE ON OBJECT::dbo.sp_update_schedule BY public),
    ADD (EXECUTE ON OBJECT::dbo.sp_delete_job      BY public),
    ADD (EXECUTE ON OBJECT::dbo.sp_add_job         BY public)
    WITH (STATE = ON);

-- read it
SELECT event_time, server_principal_name, client_ip, application_name, statement
FROM sys.fn_get_audit_file(N'L:\Audit\AgentJobChanges*.sqlaudit', DEFAULT, DEFAULT)
WHERE statement LIKE N'%enabled%' ORDER BY event_time DESC;

The statement column shows the full call, including @enabled = 0 and the job name. Forward the audit files (or write the audit to the Application log with TO APPLICATION_LOG) so your SIEM keeps them.

#Testing

EXEC msdb.dbo.sp_update_job @job_name = N'DBA - Test failure notification', @enabled = 0;
WAITFOR DELAY '00:00:05';
EXEC msdb.dbo.sp_update_job @job_name = N'DBA - Test failure notification', @enabled = 1;

You should receive two notifications (option B), or one summary at the next poll (option A), and see two audit rows (option C).

#Make it part of the process

• Planned disables (maintenance windows) should reference a change number in the job description, or be done by a script that re-enables the jobs at the end.
• Add "disabled jobs" to the weekly SQL Agent report, so nothing stays disabled by accident.

Tip

The jobs that matter most here are backups, integrity checks and index maintenance. Check that they're enabled in your daily health check, independently of any alert.