SHUTDOWN Statement

Stops SQL Server. Only the system administrator can execute this statement.

Syntax

SHUTDOWN [WITH NOWAIT]

where

WITH NOWAIT

Shuts down SQL Server immediately, without performing checkpoints in every database. SQL Server exits after attempting to terminate all user processes and roll back any active transactions.

Remarks

Unless the system administrator uses the WITH NOWAIT option, SHUTDOWN tries to shut down SQL Server in an orderly fashion by:

1. Disabling logins (except for the system administrator). To see a listing of all current users, use the sp_who system stored procedure.
2. Waiting for currently executing Transact-SQL statements or stored procedures to finish. To see a listing of all active processes and locks, use the sp_lock and sp_who system stored procedures.
3. Performing a checkpoint in every database.

Using the SHUTDOWN statement minimizes the amount of automatic recovery work needed when the system administrator restarts SQL Server.

The following tools and methods can also be used to stop SQL Server. Each of these performs a checkpoint in all databases and then a SHUTDOWN WITH NOWAIT to flush all committed data from data cache and stop the server quickly.

• SQL Enterprise Manager.
• NET STOP MSSQLSERVER from a Win32 command prompt.

SQL Server can also be stopped by the following methods. Because these methods do not perform checkpoints in each database prior to shutdown, they are not recommended and may increase the recovery time needed the next time the server is restarted. You can stop SQL Server in these additional ways:

• By using the Services application from the Windows NT Control Panel.
• By pressing ctrl+c if sqlservr.exe was started from the command-line.

Permission

SHUTDOWN permission defaults to the system administrator and cannot be transferred.

See Also