Automatically Deleting a Job

On a project my team was working on recently, we wanted to run a stored procedure every 10 minutes for 2 days to collect data over time. At the end of the 2 days, we would FTP the results up to a folder and remove any evidence that we had been on the server (clean up the tables we’d been writing to, drop the stored procedure, and in turn, drop the very job we had created to run this whole contraption.

How to delete the very job that is running?  (eyebrows raised). Is this even possible? At first we thought not. I mean, wouldn’t that tear a hole in the space-time continuum?  I asked my team to look a little deeper, there had to be a way. Maybe we could go through xp_cmdshell and call a statement that would essentially be in another process and that could do it? Let’s get creative.

Then they found it.

Automatically Delete a Job

Wow. 20 years of SQL Server and I never noticed that before. (Base of Palm to Forehead).

Thanks to Eric Blinn & Mayil Chandran for researching this and making me aware.

The following two tabs change content below.
Mindy Curnutt is a Microsoft Data Platform MVP with over 20 years of experience working with Database Solutions. She specializes in Performance Tuning and Architecture. She speaks regularly across North America at Local User Groups, SQL Saturdays and Conferences. The 3rd Thurs of most months you will likely find her at her local North Texas SQL Server User’s Group Meeting (NTSSUG) where she’s on the Board of Directors.