By: Rajendra Gupta | Updated: 2018-07-03 | Comments (5) | Related: More > Database Administration
Problem
Database administrators often reboot SQL Server after business hours if there is a need that requires downtime. Sometimes we may need SQL Server to not allow any new connections to the database, but we don't want to impact existing running queries or connections. We do not want to forcibly close these connections, but wait for the queries to finish. If we restart SQL Server all existing connections will close and SQL Server will start rolling back the running queries. In this tip, we will explore a way of not affecting existing running queries, but not any new connections.
Solution
We may need to reboot SQL Server at times to fix certain issues, tempdb reset, applying configuration changes or due to some other activity for which SQL Server should not be accessible to users. I have seen scenarios where a DBA needs to wait for the existing running queries to complete before bringing SQL Server down, but if we wait for the queries to complete, there might be a chance someone connects and starts executing another query.
We might want some time to block new connections without affecting existing connections. A reboot of SQL Server can have two choices:
- Force shutdown: Kill the sessions immediately without waiting for query completion.
- Clean shutdown: Wait for the queries to finish before shut down.
However, as stated above, if we wait for the queries to finish for a clean shutdown, there might be the possibility of new user connections.
If we shutdown the SQL Server while queries are executing, a query does not get a chance to complete and the connection gets disconnected with the below error message.
Luckily, SQL Server provides an option to pause the SQL Server services which we will look at in detail.
PAUSE in SQL Server Service
According to Microsoft (start-stop-pause-resume-restart-SQL-server-services), pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who are already connected can continue to work until their connections are broken. Use pause when you want to wait for users to complete work before you stop the service. This enables them to complete transactions that are in progress. Resume allows the Database Engine to accept new connections again.
Note: the SQL Server Agent service cannot be paused or resumed.
You can find the Pause option two ways:
- Right click on the server instance in SQL Server Management Studio as shown below.
- From SQL Server Configuration Manager, right click on SQL Server select Properties and on the General tab you will see Pause.
As described above, using Pause can stop users from making new connections, however, existing queries can run without any issues.
Let's simulate this and observe the behavior.
- Connection 1: Connect to the SQL Server, open a new query window and execute the below query. This query will take longer to execute due to multiple iterations.
SELECT * FROM [WideWorldImporters].[Sales].[Orders] Go 1000000
- Connection 2: Connect to SQL Server and open a new query window, but do not execute a query. We will execute a query in this connection later.
- Now pause SQL Server by right clicking on the server instance and selecting Pause. It opens up a pop-up window to confirm or cancel the pause operation. Click Yes to go ahead with the pause operation.
Once we click yes, it attempts to pause the SQL Server service.
- Once SQL Server is paused, we can see the pause symbol in SSMS next to the instance name. This shows the service is paused.
The service status also changes to a Paused state from a Running state.
Before Pause:
After Pause:
SQL Server is now paused.
Go to connection 1 and we can see the query is still executing.
Go to connection 2 and execute any query and it will start executing. It does not give an error because connection 2 is already open.
Try to open a new connection. To do so, open a new query window. It does not allow us to do so, because SQL Server has been paused and no new connections are allowed and it gives the below error message.
At this point, we can cleanly shut down SQL Server as soon as the existing connections complete their tasks. If the connections are taking too long to finish, we can always still stop and restart the instance.
Resume SQL Server Service
Once SQL Server is paused, we can allow new connections if needed by right clicking on the instance name and selecting Resume.
Again, it asks for confirmation before resuming SQL Server. Click Yes to proceed.
Once we click yes, the service control attempts to resume the SQL Server service.
SQL Server error logs during Pause and Resume Operations
We can see some of the events in the SQL Server error logs during pause and resume operations.
These are some of the messages you will see in the error log:
- SQL Server is not allowing new connections because the Service Control Manager requested a pause. To resume the service, use SQL Computer Manager or the Services application in Control Panel.
- SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel.
- Login failed for user ''. Reason: SQL Server service is paused. No new connections can be accepted at this time. [CLIENT: <local machine>]
- SQL Server is allowing new connections in response to 'continue' request from Service Control Manager. This is an informational message only. No user action is required.
Pause and Resume SQL Server with Commands
We can also use Windows net commands to pause and resume SQL Server. To do so, open a command prompt with administrative privileges.
Pause SQL Server
net pause MSSQL$instancename
In my case, the service name is mssql$sqlexpress. You can find the service name from the SQL Server service properties using SQL Server Configuration Manager.
Resume SQL Server
Use this command to resume allowing connections to SQL Server.
net continue MSSQL$instancename
Note: It might take longer to pause SQL Server in the case of long-running transactions. So, be careful while doing this especially in a production environment.
Next Steps
- Read more about: Monitor, Start and Stop SQL Server Services Using xp_servicecontrol
- Read more about: Start Stop Pause and Query Windows SQL Server Services Remotely
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-07-03
