Pausing SQL Server Services to Stop New Connections

By:   |   Comments (5)   |   Related: More > Database Administration


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.


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.
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:


After Pause:


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.


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.


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.

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta 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

Comments For This Article

Tuesday, April 28, 2020 - 5:54:50 AM - Arnav Back To Top (85486)

Very useful in Always On restoration..


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

 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.



Wednesday, July 4, 2018 - 9:52:08 AM - Maneesh Back To Top (76523)

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

Wednesday, July 4, 2018 - 2:36:56 AM - javed Back To Top (76518)

 Thanks for shring for a usefull tip.

Tuesday, July 3, 2018 - 9:54:46 AM - Atul Mathur Back To Top (76514)

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.

get free sql tips
agree to terms