September 15, 2016 at 9:45 am
Hi all,
I am using SQL server 2014 enterprise.
Been reading about patching and i am thinking of doing a proper shutdown before the patch.
Is there any difference between doing
a) shutdown using service/server configuration manager (e.g. right click on instance name -> stop) and
issuing SHUTDOWN using T-SQL ?
b) in T-SQL there is a SHUTDOWN [WITH NOWAIT] option (force shutdown), is there such option when using service/server configuration manager ?
c) when issuing a SHUTDOWN, it does not matter which database you are currently accessing; it will shutdown the whole instance.
d) can i say the difference between a PAUSING an SQLinstance, and doing a SHUTDOWN, is that
i) with pausing, no new logins is allow but with SHUTDOWN (sysadmin, serveradmin role) still can login
ii) with pausing, existing sessions can stay as long as they like , but with SHUTDOWN, sessions are ended automatically once their current transaction is completed
is my above understanding correct, did i miss out any other options for shutting down ?
Regards,
Noob
September 15, 2016 at 10:35 am
There's no real need to shut down the instance externally. When you apply a SQL patch, the software will restart the instance for you by itself.
September 15, 2016 at 11:44 am
Hi Kris,
Understood that. Have tried it out and the patch did stop and start up the instance for me.
But i would still prefer to do a proper shutdown before the patch and not depending on the patch to shutdown for me.
Are you able to shed some light on my questions in the earlier thread ?
Regards,
Noob
September 15, 2016 at 12:05 pm
There isn't a lot of difference with the different methods to shutdown SQL Server. The one exception being SHUTDOWN with NOWAIT. It's not a good thing and doesn't lend itself to a clean shut down. You'd want to avoid that and really only use that for extreme emergencies of some sort.
You can execute SHUTDOWN in t-sql in any database.
When Pausing, Existing logins can continue operations for however long. No new connections are allowed.
SHUTDOWN just waits for currently executing statements to finish and then the sessions ends.
Other ways to shut down would include using Powershell and doing it from the command line with net stop.
Sue
September 15, 2016 at 12:07 pm
Patching the instance does shut it down the 'proper' way and restarts it.
The 'proper' way to shut down an instance is pretty much anything besides going and killing the process in task manager. Take your pick, net stop, SQL configuration manager and patching all do clean shutdown.
SHUTDOWN WITH NOWAIT shuts down the instance but does NOT checkpoint the DBs before shutting them down. When it restarts, SQL server will have to rollback transactions that were in flight. I've never experienced a time where I had to use this method.
September 15, 2016 at 12:33 pm
Rather run the patch and let it manage the instance for you. Patches often contain T-SQL that has to be run in addition to the binaries changes.
If you absolutely insist on shutting down first, use the Services manager or the SQL Configuration Manager, it's the easiest.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2016 at 2:08 am
thank you guys! I guess I will patch it without shutting down 1st 😛
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply