Pausing a SQL Agent job safely: disable the job, the schedule or a step?

#The three switches

#Disable or enable the job

EXEC msdb.dbo.sp_update_job @job_name = N'ETL - Nightly Sales', @enabled = 0;
-- later
EXEC msdb.dbo.sp_update_job @job_name = N'ETL - Nightly Sales', @enabled = 1;

Use this for most cases: the job is clearly marked as disabled in SSMS and in monitoring.

#Disable just the schedule

Useful when the job should still run on demand (from an app or another job) but not on its timer.

-- Check whether the schedule is shared first
SELECT s.name AS schedule_name, j.name AS job_name
FROM msdb.dbo.sysschedules s
JOIN msdb.dbo.sysjobschedules js ON js.schedule_id = s.schedule_id
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE s.schedule_id IN (
  SELECT js2.schedule_id FROM msdb.dbo.sysjobschedules js2
  JOIN msdb.dbo.sysjobs j2 ON j2.job_id = js2.job_id
  WHERE j2.name = N'ETL - Nightly Sales');

EXEC msdb.dbo.sp_update_schedule @schedule_id = 42, @enabled = 0;

Warning

If more than one job appears in the result, disabling the schedule stops all of them. Detach and give the job its own schedule instead.

#Skip one step

To skip step 3 temporarily, have step 2 go straight to step 4 on success:

EXEC msdb.dbo.sp_update_jobstep @job_name = N'ETL - Nightly Sales', @step_id = 2,
     @on_success_action = 4, @on_success_step_id = 4;   -- 4 = go to step

Record the original values so you can put them back. It's easy to forget a skipped step.

#Find jobs left disabled

SELECT j.name, j.enabled AS job_enabled, s.name AS schedule_name, s.enabled AS schedule_enabled,
       j.date_modified
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id
WHERE j.enabled = 0 OR s.enabled = 0
ORDER BY j.date_modified;

Run this weekly or add it to your health report. A backup job disabled "for an hour" six weeks ago is a classic finding.

#Habit: say why in the description

EXEC msdb.dbo.sp_update_job @job_name = N'ETL - Nightly Sales', @enabled = 0,
     @description = N'DISABLED 2026-10-25 by DBA for CHG-1234 (source system upgrade). Re-enable after 2026-10-27.';

The reason and the expected re-enable date are then visible to anyone who opens the job.

#Prevent it next time

• Alert when jobs are enabled or disabled (see the job enable/disable alert post).
• Never share schedules between unrelated jobs.
• Include disabled jobs and schedules in the weekly report.