One-off SQL Agent jobs that clean up after themselves: delete_level, end dates and a sweeper

#The clutter problem

Open SSMS on an old server and you'll find test, Copy of ETL - Load, temp rebuild Dave and one time fix 2019. Each one is a risk: someone enables it, or a schedule fires it again.

#Option 1: the job deletes itself (@delete_level)

EXEC msdb.dbo.sp_add_job @job_name = N'ONEOFF - Rebuild IX_Orders 2026-10-25',
     @owner_login_name = N'sa', @delete_level = 1,     -- delete only if it succeeds
     @description = N'One-off for CHG-2231. Self-deletes on success.';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'ONEOFF - Rebuild IX_Orders 2026-10-25', @step_name = N'Rebuild',
     @database_name = N'Sales',
     @command = N'ALTER INDEX IX_Orders_Date ON dbo.Orders REBUILD WITH (ONLINE = ON);';

EXEC msdb.dbo.sp_add_jobschedule @job_name = N'ONEOFF - Rebuild IX_Orders 2026-10-25', @name = N'Once',
     @freq_type = 1, @active_start_date = 20261025, @active_start_time = 230000;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'ONEOFF - Rebuild IX_Orders 2026-10-25';

Warning

When a job deletes itself, its history is deleted too. Use @delete_level = 1 (on success) so failed runs stay for investigation, and send a notification or write an output file if you need a record of success.

#Option 2: an end date for temporary recurring jobs

A job that should run nightly during a three-week migration:

EXEC msdb.dbo.sp_add_jobschedule @job_name = N'TEMP - Sync Legacy Customers', @name = N'Nightly until 2026-11-15',
     @freq_type = 4, @freq_interval = 1, @active_start_time = 020000,
     @active_end_date = 20261115;

After the end date the schedule stops firing, but the job still exists. The sweeper below removes it.

#Option 3: a weekly sweeper job

Use a naming convention (ONEOFF - and TEMP -) and a sweeper that removes old ones:

DECLARE @job_id uniqueidentifier, @name sysname;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT j.job_id, j.name
  FROM msdb.dbo.sysjobs j
  WHERE (j.name LIKE N'ONEOFF -%' OR j.name LIKE N'TEMP -%')
    AND j.date_modified < DATEADD(DAY, -30, GETDATE())
    AND NOT EXISTS (                         -- not running
      SELECT 1 FROM msdb.dbo.sysjobactivity a
      WHERE a.job_id = j.job_id
        AND a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
        AND a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL)
    AND NOT EXISTS (                         -- no future run planned
      SELECT 1 FROM msdb.dbo.sysjobschedules js
      JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id
      WHERE js.job_id = j.job_id AND s.enabled = 1
        AND js.next_run_date >= CONVERT(int, CONVERT(char(8), GETDATE(), 112)));
OPEN c; FETCH NEXT FROM c INTO @job_id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT N'Deleting ' + @name;
  EXEC msdb.dbo.sp_delete_job @job_id = @job_id, @delete_unused_schedule = 1;
  FETCH NEXT FROM c INTO @job_id, @name;
END
CLOSE c; DEALLOCATE c;

Run it in report-only mode (comment out the delete) for a few weeks first.

#Finding existing clutter

SELECT j.name, j.enabled, j.date_created, j.date_modified,
       (SELECT MAX(run_date) FROM msdb.dbo.sysjobhistory h WHERE h.job_id = j.job_id) AS last_run_date
FROM msdb.dbo.sysjobs j
WHERE j.name LIKE '%test%' OR j.name LIKE '%temp%' OR j.name LIKE '%copy of%'
   OR j.name LIKE '%old%' OR j.enabled = 0
ORDER BY j.date_modified;

Script each candidate out before deleting it (right-click → Script Job as → CREATE), and keep the scripts for a while.

#Prevent it next time

• Naming convention for temporary jobs, enforced in review.
• @delete_level = 1 for one-offs, end dates for temporary schedules.
• Sweeper job plus a quarterly review of disabled jobs.