Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Pausing SQL Server Services to Stop New Connections


By:   |   Last Updated: 2018-07-03   |   Comments (4)   |   Related Tips: 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.

messages

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.
pause
  • From SQL Server Configuration Manager, right click on SQL Server select Properties and on the General tab you will see Pause.
sql server

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.
management studio

Once we click yes, it attempts to pause the SQL Server service.

service control
  • Once SQL Server is paused, we can see the pause symbol in SSMS next to the instance name. This shows the service is paused.
sql express

The service status also changes to a Paused state from a Running state.

Before Pause:

running

After Pause:

paused

SQL Server is now paused.

Go to connection 1 and we can see the query is still executing.

sql query

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.

background

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.

database engine

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.

object explorer

Again, it asks for confirmation before resuming SQL Server. Click Yes to proceed.

management studio

Once we click yes, the service control attempts to resume the SQL Server service.

service control

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.

server

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.

recovery
command prompt

Resume SQL Server

Use this command to resume allowing connections to SQL Server.

net continue MSSQL$instancename			
sql express

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


Last Updated: 2018-07-03


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 31, 2018 - 9:27:29 AM - Srini Back To Top

 Awesome. It is really helpful in the situations where the SQL Server is facing the highly connection traffic situations.

Thanks for your valuable post with detailed snapshots.

Srini

DBA


Wednesday, July 04, 2018 - 9:52:08 AM - Maneesh Back To Top

Thanks for sharing.Nice to have  practical use of pause feature.


Wednesday, July 04, 2018 - 2:36:56 AM - javed Back To Top

 Thanks for shring for a usefull tip.


Tuesday, July 03, 2018 - 9:54:46 AM - Atul Mathur Back To Top

Great post on how to use an existing feature, never thought pause could be used for keeping new connections from being established.

Need to add, pausing clusters using Configuration Manager or Management Studio may initiate a failover of SQL from one node to another. I have had success by pausing the service on active node.

Also, shutting down SQL Agent before pausing SQL can avoid unnecessary alerts being generated.


Learn more about SQL Server tools