Shutting Down SQL Server

SQL Server is one of the most reliable database systems available. But no matter how reliable your system is, you occasionally need to shut it down for a variety of reasons, including planned maintenance or relocation. Closing Windows causes SQL Server to shut down, but if that's not an option for you, here are seven other ways that you can shut down SQL Server.

7. Enterprise Manager

The first shutdown method that most DBAs think of is using Enterprise Manager. Open Enterprise Manager, then right-click the server you want to stop. Select the Stop option from the pop-up menu to stop the selected server.

6. SQL Server Service Manager

The SQL Server Service Manager applet is available only on the SQL Server system. Open the applet by double-clicking its icon on the taskbar. Then, from the drop-down menu, select the name of the SQL Server system you want to stop, and click Stop.

5. Windows Services

Open the Windows Services applet by selecting Start, Programs, Administrative Tools, and Services. Double-click the MSSQLServer service (or the MSSQL$InstanceName service, if you have a named instance), and click Stop to stop the instance that you've selected.

4. The Net Stop Command

The previous three shutdown methods rely on the Windows GUI. But to stop SQL Server from outside the Windows GUI, you can use Windows 2000 or Windows NT 4.0's built-in Net command. From a command prompt, type net stop mssqlserver. If you're using a named instance, type net stop mssql$instancename. Note that Net Stop doesn't work with clustered SQL Server systems. To shut down a clustered system, you need to use the Microsoft Cluster Service (MSCS) cluster.exe utility.

3. The T-SQL SHUTDOWN Statement

The T-SQL SHUTDOWN statement performs an orderly shutdown of the server, with SQL Server checkpointing all databases and flushing all committed data to disk. You can also use the SHUTDOWN WITH NOWAIT statement to perform an immediate shutdown without checkpointing the databases. Like Net Stop, the T-SQL SHUTDOWN command doesn't work with clustered SQL Server systems.

2. SQL-DMO

SQL Distributed Management Objects (SQL-DMO) is a COM-based management framework that can perform nearly every SQL Server management function. To use SQL-DMO to stop SQL Server, create an instance of the SQLServer2 object, then execute the SQLServer2.Shutdown method.

1. WMI

SQL-DMO must be installed on every client, but you need to install the Windows Management Interface (WMI) provider for SQL Server only on the SQL Server system. You install WMI from the x86otherwmi directory on the SQL Server CD. After you create an instance of the WMI MSSQL_SQLServer object, you can invoke the MSSQL_SQLServer.Stop method to stop the SQL Server service.

For more information, see "Starting SQL Server" and "Securing SQL Server."