I have read different opinions on whether to manually shut off the SQL Agent service before shutting down a SQL instance for 2 days; some believe the server being off is perfectly fine, and others have said to manually stop the Agent and manually start when the server/instance is turned back on. To me the server will be off, so no need to manually stop the agent service. Which do you prefer?
Also, it's my understanding that the SQL Instance will not try to run "old" jobs that occurred when the server was down, such as the full-backups that run on Fridays. Is is true the server will not try to run the jobs it missed during the outage? (Either by manually stopping the Agent service or by just shutting down the server)?
March 9, 2021 at 4:59 pm
If SQL service itself is going to be stopped, then you should definitely stop the Agent service first.
I'm not sure if the Agent would try to run missed jobs later after the SQL service itself came back on. Likely not, but since I don't let the Agent run without SQL itself running, I don't have any real experience with this, it's just an educated guess.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 9, 2021 at 10:49 pm
Scott - Thanks for the reply. So when you stop the Agent, what happens to the jobs that were supposed to run during the stoppage? That's mainly what I'm concerned about. I don't want jobs from the past becoming retro-active.
If you stop the Agent, it will not go back into the past to run jobs when it is restarted.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 9, 2021 at 10:55 pm
Scott - Thanks for the reply. So when you stop the Agent, what happens to the jobs that were supposed to run during the stoppage? That's mainly what I'm concerned about. I don't want jobs from the past becoming retro-active.
The agent does not try to start jobs whose start time has passed when it is restarted. The agent will calculate the next start time - and the job will start at that time. It will not be started immediately when the agent restarts.
The only time I ever manually stop the agent is when I want to make sure the agent (and jobs) do not start when the server is restarted. Then, you need to change the startup from automatic to manual or disabled to prevent the agent from automatically starting when the server is restarted. Other than that specific scenario, shutting it down or letting Windows issue the shut down makes no difference.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 9, 2021 at 11:43 pm
stevec883 wrote:Scott - Thanks for the reply. So when you stop the Agent, what happens to the jobs that were supposed to run during the stoppage? That's mainly what I'm concerned about. I don't want jobs from the past becoming retro-active.
The agent does not try to start jobs whose start time has passed when it is restarted. The agent will calculate the next start time - and the job will start at that time. It will not be started immediately when the agent restarts.
The only time I ever manually stop the agent is when I want to make sure the agent (and jobs) do not start when the server is restarted. Then, you need to change the startup from automatic to manual or disabled to prevent the agent from automatically starting when the server is restarted. Other than that specific scenario, shutting it down or letting Windows issue the shut down makes no difference.
The Agent service depends on the SQL Instance service. For a clean shut down, you want to the shut the dependent service -- the Agent -- yourself if possible, or, yeah, you can let Windows do it for you if you're using the services gui to explicitly shut down the instance service yourself.
Btw, if you know the shutdown is coming, you can speed up the restart by issuing an explicit CHECKPOINT in all dbs immediately prior to the shut down. For example:
EXEC sp_msforeachdb 'USE [?]; CHECKPOINT;'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 10, 2021 at 4:05 pm
Thanks for the reply Jeffrey - that makes sense now.
March 10, 2021 at 5:58 pm
Jeffrey Williams wrote:stevec883 wrote:Scott - Thanks for the reply. So when you stop the Agent, what happens to the jobs that were supposed to run during the stoppage? That's mainly what I'm concerned about. I don't want jobs from the past becoming retro-active.
The agent does not try to start jobs whose start time has passed when it is restarted. The agent will calculate the next start time - and the job will start at that time. It will not be started immediately when the agent restarts.
The only time I ever manually stop the agent is when I want to make sure the agent (and jobs) do not start when the server is restarted. Then, you need to change the startup from automatic to manual or disabled to prevent the agent from automatically starting when the server is restarted. Other than that specific scenario, shutting it down or letting Windows issue the shut down makes no difference.
The Agent service depends on the SQL Instance service. For a clean shut down, you want to the shut the dependent service -- the Agent -- yourself if possible, or, yeah, you can let Windows do it for you if you're using the services gui to explicitly shut down the instance service yourself.
Btw, if you know the shutdown is coming, you can speed up the restart by issuing an explicit CHECKPOINT in all dbs immediately prior to the shut down. For example:
EXEC sp_msforeachdb 'USE [?]; CHECKPOINT;'
That may have been true in older versions of SQL, but Windows / SQL is smart enough these days to stop the agent first, then shut down SQL.
Yu can prove that by right clicking in Configuration manager, and stop the SQL Server service. It will prompt you that other services will also be re-started, and shut them down first.
Shut the box down, there's no real need for an extra step.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 10, 2021 at 10:17 pm
Thanks Michael -- the checkpoint command will come in handy for sure.
March 11, 2021 at 3:40 am
Thanks Michael -- the checkpoint command will come in handy for sure.
I pointed out CHECKPOINT.
I have servers with hundreds of dbs. If you just slam them down, the recovery time on start up is much lengthier than if you cleanly shut them down.
The preferred method is to issue the SHUTDOWN command in SQL, which will do checkpoints.
I'm virtually certain Windows does the equivalent of a SHUTDOWN WITH NOWAIT, which does not use CHECKPOINTs and can, again, cause longer times for startups and potentially more lost transactions.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply