Automatic cleanup of SQL Server Agent scheduled jobs

By:   |   Comments (3)   |   Related: > SQL Server Agent


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.


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.

new job properties SQL 2005

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, October 20, 2022 - 1:52:32 AM - Chandu Patel Back To Top (90620)
For the last few days, a few of my scheduled jobs aren't triggering, and no history/error was recorded, even I checked in the error log path file but no error was recorded, I renamed those jobs are retriggered still no use, but a few jobs are working properly, even I scripted same jobs from a different server and executed it after changes have been done accordingly to this current server but still no use, but when I trigger it manually "start a job at step'' works fine, can someone please help me with this, even I restarted agent off business hours still no use? please help me with this issue

Monday, April 12, 2021 - 8:42:30 AM - Santiago Back To Top (88512)
Interesting article. But I think SQL Server Agents are missing an important piece. Prevent an unused Schedule when the last job is set to be deleted after execution.
sp_delete_job has the parameter; but sp_add_job, is missing that when using the parameter delete_level > 0

You might want to delete the job, but not necessarily the schedule...

Wednesday, September 12, 2012 - 3:17:38 PM - Grega Back To Top (19492)


We actively use functionality of job creation and deletion for 1 time tasks.
Logs are full of "Job being succesfully deleted" on our sql 2005 server.
How do we omit these logs from being created ?



get free sql tips
agree to terms