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

  • sgmunson

    SSC Guru

    Points: 110494

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Jeff Moden

    SSC Guru

    Points: 996810

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

    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: 720371

    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: 110494

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

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

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