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!
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.
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....
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.
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?
Change is inevitable... Change for the better is not.
This was removed by the editor as SPAM
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 6 (of 6 total)