Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automatic cleanup of SQL Server Agent scheduled jobs


By:   |   Last Updated: 2006-12-20   |   Comments (1)   |   Related Tips: More > 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
  • Whenever the job completes

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

Here are screen shots for each version.

SQL Server 2000

new job properties SQL 2000

SQL Server 2005

new job properties SQL 2005

In addition to using the GUI you can also use the stored procedure sp_add_job for both SQL Server 2000 and 2005. 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

Note: this will create the job and set the delete level, but because there are no job steps this job will not run.  You will also need to use the 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 function will allow you to automatically create and cleanup the jobs after execution.

Next Steps


Last Updated: 2006-12-20


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Hi.

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 ?

Thx
Grega

 


Learn more about SQL Server tools