How to shut down SQL Server

The question how to shutdown SQL Server came up at work last week, I figured it would be a nice topic for the newer DBAs and SQL Server programmers

Note: I assume SQL Server is not part of a clustered environment here, if your SQL Server instance is part of a cluster, then use the failover cluster manager instead!


There are a bunch of way to shut down SQL Server, here is how you can do it

Configuration Manager

To shut down SQL Server for the SQL Server Configuration Manager, navigate to your instance, right click and select Stop

SSMS Object Explorer

The process from SSMS is very similar to Configuration Manager, right click on the instance and select Stop

Shutdown command

From SSMS open a new query window and type shutdown or shutdown with nowait

Unless the WITHNOWAIT option is used, SHUTDOWN shuts down SQL Server by:

• Disabling logins (except for members of the sysadmin and serveradmin fixed server roles).
• Waiting for currently running Transact-SQL statements or stored procedures to finish. To display a list of all active processes and locks, run sp_who and sp_lock, respectively.
• Inserting a checkpoint in every database.

That seems kinda dangerous can someone pass that command to a proc by masking it?

declare @d varchar(30)= reverse('tiawon htiw nwodtuhs')
exec(@d)

The SHUTDOWN statement cannot be executed within a transaction or by a stored procedure. Msg 0, Level 11, State 0, Line 3 A severe error occurred on the current command. The results, if any, should be discarded.

That is good to know  :-)


Command prompt

Open a command prompt as administrator and type net stop MSSQLSERVER

Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>net stop MSSQLSERVER
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.


C:\WINDOWS\system32>net start MSSQLSERVER
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.


C:\WINDOWS\system32>

To start SQL Server again, change net stop to net start


Below are a couple of more ways but you should really not be using those!!

Task Manager

Yes, you can kill SQL Server from task manager

Taskkill

You can use the plain old DOS command taskkill as well

Shutdown your server
That is one way of course but you might get a bunch of people yelling at you

Unplug the power to your server after making sure you have no UPS battery backup plugged in
Great way to test recovery... .. sarcasm people.....

Shutdown the data center
You laugh but this actually happened to me once, someone did some work and shut down the whole development part of the data canter. We had SQL Server 2000 running on NT 4, it was not restarted in 6 years (meaning wasn't patched either) It was an isolated box, internal ip, couldn't be reached from outside the dev network


There you have all the ways that you can use to shut down SQL Server