Run critical job during upgrade window - SQL Agent stopped

Question

Run critical job during upgrade window - SQL Agent stopped

Squatt Gmail

SSC-Addicted

Points: 422
More actions
May 26, 2020 at 10:10 pm

#3757564

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
May 27, 2020 at 3:19 am

This was removed by the editor as SPAM

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 More actions · Answer 2

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: 90860 More actions · Answer 3

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: 1003865 More actions · Answer 4

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.

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

Squatt Gmail SSC-Addicted Points: 422 More actions · Answer 5

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.