Gracefully shutdown SQL Server, stop accepting new connections

During maintenance activity we try to bring down SQL server gracefully without impacting existing transactions and not accepting new connections. One of the best options to accomplish this is to PAUSE SQL Service using SQL Server configuration Manager.

Pausing SQL Service using Server configuration Manager will not allow SQL Server to accept new connections but leave all existing connections as-is until they are closed, DBA can validate when the SQL Server can be shutdown. This minimizes end user impact, none of the transactions needs to be rolled back.

How to Pause:
SQL_Pause_01

We have option to either start the Service, or Stop the service or Restart the Service:
SQL_Pause_02

Error message while establishing new connections during this stage:
SQL_Pause_03

Error Message:
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 ”. (Microsoft SQL Server, Error: 17142)

NOTE:
The option is verified on Stand Alone SQL Server.
Make sure to use SQL Server Configuration Manager rather than using windows services services.msc to pause the service

Ref: http://sqlstudies.com/2014/09/04/pausing-an-mssql-instance/

Advertisements
This entry was posted in Productivity, SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s