Can a SQL Agent successfully run a job step that deletes that job?

Question

Can a SQL Agent successfully run a job step that deletes that job?

sgmunson

SSC Guru

Points: 110640
More actions
February 26, 2016 at 8:26 am

#308823

I'm curious to know if a SQL Agent job can have a job step that will delete that very job, effectively making itself go away. One-time use scenario. Or is there an easier way to run as SSIS package just once? Don't want that package hanging around in job form...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Yes... you can make a "self-deleting" job. When you're setting up, look under "Notifications" for the "Automatically Delete Job" check box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

sgmunson (2/26/2016)
Or is there an easier way to run as SSIS package just once? Don't want that package hanging around in job form...

From what context are you trying to start the Package, Stored Proc, Agent Job, other?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 3

In a previous job, I inherited an environment where a developer did that as part of an automated process. Made it more difficult to track down and troubleshoot than necessary.

I would suggest running it via t-sql, dtexec, SSMS or visual studio if it's a one time thing.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I don't mind one time jobs, but don't have them delete right away. I want the history and being able to look in the short term.

We had a few one time jobs, we'd just disable, because we could use them later (weeks/months) to run a different process, and we'd change the job then and set for one time. I also ran into someone that had jobs scheduled for one occurrence (do this tonight), and they'd have a separate job that deleted any one time jobs that had completed over a week ago.

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Thanks for the responses everyone. I did not know about the option within the Notifications tab, which is interesting. I had not yet had a chance to run my self-deleting job at the point I made the post, and it turns out it ran sooner than expected, and it did indeed delete itself. I simply had the last step execute sp_delete_job. It looks cleaner to have it done via the notifications tab, so that's probably how I'll set it up. We just can't afford to have the things this job does run more than once, and don't want the job hanging around any longer than absolutely necessary.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)