Run critical job during upgrade window - SQL Agent stopped

  • Squatt Gmail

    SSC-Addicted

    Points: 422

    Hello,  I would like to stop the SQL Server Agent on our ETL box while a 3rd party is updating their software database that contains most of our transaction data.  I have run a test on our Test ETL server to see if it is possible to run a job using sp_start_job while the SQL Agent is stopped.  I get the 22022 error about SQL Agent not being notified of the action but does this mean the job didn't run or it just wasn't able to log the activity?  I know I have the option of manually executing packages from my machine to accomplish the same results but I consider this a "learning opportunity" as in 20+ years of experience, I have not had to do something as drastic as shut down the SQL Server Agent AND still run a few packages.  Out of 100s of jobs, there are only a half dozen or so that must run due to compliance - Medicare/Medicaid....

    SQL Server Version: 2016 (13.0.5366.0), Windows 2012 R2 and Package version 2016

    Thanks for your advice!

    Scott

  • Sue_H

    SSC Guru

    Points: 90673

    If Agent isn't running, the jobs won't run. The error tells you that Agent isn't running so it couldn't be told to run the job.

    Sue

  • Squatt Gmail

    SSC-Addicted

    Points: 422

    OK, I trust your GURU status 🙂  I was hoping it the job could be run, just without the updating of MSDB tables.  Guess it manually from my laptop instead....

  • Sue_H

    SSC Guru

    Points: 90673

    I don't trust my status :)....but the agent process actually runs the jobs so Agent needs to be running. It's a pain but yeah you'll need to run those manually.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 996661

    Why not write a query that finds all enabled jobs, makes a list (table of them), and then disable all jobs except for that critical one you're talking about?

    --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)

  • This was removed by the editor as SPAM

  • Squatt Gmail

    SSC-Addicted

    Points: 422

    I have actually created a table that list all jobs and schedules that are scheduled during the outage window and we are prioritizing them, talking to business owners to understand the impact of them running or not so that is how I boiled down to the few that are critical.  I was just hoping I could shut it all down for safety and then run the couple required jobs manually on the server since we are all working remotely (as I'm sure most of us are) to speed things up.  I can use a jump server to run the process so at least it is running on the internal network.

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

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