SQL SERVER – Error: 17142 – SQL Server Service has Been Paused. No New Connections Will be Allowed

Recently I was looking around in my virtual machine to find a topic to blog. Within a few minutes, I was having an idea. In this blog we would talk about an interesting feature “Pause SQL Server”. As stated via official Microsoft documentation “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 pauses when you want to wait for users to complete the work before you stop the service. This enables them to complete transactions that are in progress”. Let us learn about how to fix the issue when the new connections are not allowed.

I did some experiments and sharing it via this blog. First, here is the error message when we try to connect to a SQL instance, which is in a paused state.

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)

Similar message would come in the ERRORLOG as well.

2018-01-01 14:55:33.820 spid56 Error: 17142, Severity: 14, State: 0.
2018-01-01 14:55:33.820 spid56 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.
2018-01-01 14:55:33.820 Logon Error: 18456, Severity: 14, State: 13.
2018-01-01 14:55:33.820 Logon Login failed for user ”. Reason: SQL Server service is paused. No new connections can be accepted at this time. [CLIENT: localhost]

If you check configuration manager, you will see below

The funny thing about error message is it doesn’t talk about configuration manager. It asks to use servies.msc to resume the service.

WORKAROUND/SOLUTION

Either follow error message and resume the SQL Service via service applet (start > run > services.msc) or use the SQL Server Configuration Manager.

Once I resumed it, the error message showed below message.

2018-01-01 14:56:02.510 Server       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.

Reference : Pinal Dave (https://blog.sqlauthority.com)