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 - Error: 17142 - SQL Server Service has Been Paused. No New Connections Will be Allowed sql-pause-01

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)

Solarwinds

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

SQL SERVER - Error: 17142 - SQL Server Service has Been Paused. No New Connections Will be Allowed sql-pause-02

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.

SQL SERVER - Error: 17142 - SQL Server Service has Been Paused. No New Connections Will be Allowed sql-pause-03

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Slow Filestream Data Cleanup. What Should We Do?
Next Post
SQL SERVER – Introduction to Log Space Usage DMV – sys.dm_db_log_space_usage

Related Posts

1 Comment. Leave new

  • Is there any other workaround other than resuming the SQL server services if some user wants to connect.

    Reply

Leave a Reply

Menu