By: Greg Robidoux | Updated: 2021-04-16 | Comments (3) | Related: > SQL Server Agent
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
About the author
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 MSSQLTips.com. 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
Article Last Updated: 2021-04-16
