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 |  |
| Fill-in the required details on the “General” tab |  |
| In the “Steps” tab, add the necessary steps.
For our demonstration, we will only use a single rebuild index step |
  |
| 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. |
 |
| Notice that our job is now successfully created |  |
| Run the job manually
(For our demo, we have not established Job schedules. In the real world, you would almost always have scheduled jobs.) |
 |
| Confirm that the indexes were rebuilt as requested |  |
| 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 |  |
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!
SQLTwins by Nakul Vachhrajani 