Automatic cleanup of SQL Server Agent scheduled jobs

Problem

One problem that people often have is a large number of SQL Agent jobs on their servers and they don’t want to remove them because they are not exactly sure what the jobs are used for or why they were created.  In most cases a lot of these jobs were created to run one time, but no one took the time to enter a description or better yet delete the job after it was run. So because of this you end up with a lot of extra scheduled jobs that you probably don’t really need.

Solution

If you have such jobs that you only want to run one time and then have the job automatically deleted after it is finished you can use the “Automatically delete job:” option when you create the job. This can be done either with the GUI tools or using stored procedures. There are three options for this setting:

• When the job succeeds
• When the job fails
• Whenr the job completes

When using SQL Server Management Studio if you go to the Notifications section for setting up a job you can set this option.

In addition to using the GUI you can also use the stored procedure sp_add_job. This command in its simplest form can be run as follows to set this option:

 EXEC sp_add_job @job_name = 'Test Job Auto Delete', @delete_level = 1

This will create the job and set the delete level (note: because there are no job steps this job will not run).  You will also need to use sp_add_jobstep to add the job steps or you can do this using the GUI after you create the job with the SP.

The options for @delete_level are:

• 0 – do not delete job
• 1 – delete when job succeeds
• 2 – delete when job fails
• 3 – delete when job completes

Although the main premise for this tip was to cleanup old jobs, this approach can also be used to automate the creation and deletion of jobs.  You may have a batch process that runs every night and there is a need to create different job options each night.  Using these stored procedures along with this delete functionalty will allow you to automatically create and cleanup jobs after execution.

Next Steps

• Next time you create a job see if this option makes sense to assist in the maintenance of  your jobs
• Take a look at these stored procedures for creating jobs
  • sp_add_job
  • sp_add_jobstep
  • sp_add_schedule

Greg Robidoux

Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.