SQL Server – SQL Agent – Jobs – Automatically delete SQL Server Agent jobs


I would start today’s post with a question – Have you ever faced a requirement to delete a job once it successfully completes?

Once I had to achieve just that – a SQL Server job had to be created, and the requirement was to delete the job after it’s execution. Now, the job was a scheduled data cleanup operation in a client-server system whose code-fix had already been deployed previously and therefore there was no need to run the job ever again. However, we were not able to run it during the day because users were in the system. We needed to run the job before the nightly end-of-day routines executed, and at a time we knew that nobody would be accessing the system. So, here is what we did.

From the Object explorer, navigate to the SQL Server Agent, and start the creation of a new nightly job image
Fill-in the required details on the “General” tab image
In the “Steps” tab, add the necessary steps.

For our demonstration, we will only use a single rebuild index step

image  
image
Script used in the job:
/*
WARNING : THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
                     WARRANTY.
                     THE SCRIPT IS INTENDED FOR DEMOSTRATION
                     PURPOSES ONLY.
*/
USE AdventureWorks2008R2
GO
ALTER INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail
REBUILD;
GO

Navigate out to the Notifications tab and Check the checkbox – “Automatically delete job”.

As you can see we have 3 options to delete the job when:

1. The job succeeds

2. The job fails

3. The job completes

We will choose to delete the job upon success.

image
Notice that our job is now successfully created image
Run the job manually

(For our demo, we have not established Job schedules. In the real world, you would almost always have scheduled jobs.)

image
Confirm that the indexes were rebuilt as requested image
Script used for the verification
USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Sales.SalesOrderDetail'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
Verify that the job has been deleted image

I trust that you found the post interesting and that the above mentioned method of automatically deleting a SQL Server agent job will be of help to you someday.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s