What is the estimate time for stopping an SQL Server Instance?
If the environment you administer is in production, it is quite possible that you wondered, at least once, how much time it will take if I have to stop the SQL Server Instance?
And the answer is : depends.
When you send the command to stop an SQL Server instance, the following steps are performed by the SQL Server Engine:
· Disables logins (except for system administrators);
· Performs a CHECKPOINT in every database. However, if you stop SQL Server using CTRL+C at the command prompt, it does not perform a CHECKPOINT in every database. Therefore, the recovery time is increased the next time the server is started.
· Waits for all currently executing Transact-SQL statements or stored procedures to finish;
You can pause SQL Server before stopping the server. Pausing SQL Server prevents new users from logging in and gives you time to send a message to current users asking them to complete their work and log out before you stop the server.
If you stop SQL Server without pausing it, all server processes are terminated immediately. Stopping SQL Server prevents new connections and disconnects current users.
The steps are also described in the following white paper: Chapter 1 - Starting, Pausing, and Stopping SQL Server
So we have 3 variables that could influence the time for shutting down and SQL Server instance.
First depending on the number of logins that are currently logged the steps is done in a proportional time. That means , if you have billions of users logged then the step of 'disable logins' could take a while. How much depends on the resources available and on the charge of the server.
The second step is a bit tricky. If you have a lot of databases, and we know that one instance of SQL Server can have up to 32,767 databases Create a Database, than the CHECKPOINT operation for every database can take a lot of time, Database Checkpoints. This in case all the databases are online. If some of the databases are offline than these will not be counted.
For last step 'Waits for all currently executing Transact-SQL statements or stored procedures to finish' we have to monitor the entire activity on the server. So if at the moment you want to shutdown the instance you have a lot of activity on the databases then, the stop will be done once all the transactions and the procedures are successfully executed.
And here are the recommendations that I can provide to be sure you have the minimum time required for shutting down an SQL Server Instance.
- Make sure all the applications, that are using the SQL Server databases are stopped;
- If you can, you can put some of the databases that you are not using offline;
- Make sure you have available resources on the server that SQL Server Instance resides;
Keep in mind that you have the following options, described in the same white paper as mentioned above or in the article Stopping Services, to stop an SQL Server Instance:
You can stop Microsoft SQL Server locally from the server or remotely from a client or another server using:
- SQL Server Configuration Manager;
- SQL Server Management Studio;
- SHUTDOWN statement;
- net commands
- Control Panel;
- CTRL+C
It is highly recommended to test the recommendations mentioned above and also to verify the time for shutting down an SQL Server Instance on an test environment (identical with the production environment) before doing the same actions on production environment.
If you do not stop the applications before shutting down the SQL Server Instance than I suggest you broadcast a message to warn users of an impending shutdown. In the message, include the time the instance of SQL Server will be stopped so that users can finish their tasks. For more information, see How to: Broadcast a Shutdown Message (Command Prompt).