Sometimes making a change in something for which the impact is not known, could cause a heavy loss. A similar situation happened when one of my clients called me and told that he is in big trouble and losing a lot of money every minute. Without wasting any moment, I asked what is the issue, then he told that he changed some setting in SQL Server, restarted SQL and now no one is able to connect. The application is completely down! Let us see how we can fix the error: Could not connect because the maximum number of ‘1’ user connections has already been reached.
We quickly started desktop sharing and we noticed the below error while connecting from SSMS.
TITLE: Connect to Database Engine
Cannot connect to DBSERVER.
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
No process is on the other end of the pipe
This was a very generic error, so I asked to check the Application Event Log.
Log Name: Application
Event ID: 17809
Task Category: Logon
Could not connect because the maximum number of ‘1’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed.
As per above message the number of user connections was set to 1 and all we need to do is change that value to 0 as shown below
Sounds simple, but the situation here was that someone was grabbing connection so quickly that we were not able to connect. I recalled an earlier blog, where I wrote about single user mode.
So, we followed the blog, start SQL with /mSQLCMD parameter, connected to the SQL server instance in single user mode via SQLCMD and then executed following command.
exec sp_configure 'user connections',0 go reconfigure with override go
Then we restarted SQL Server and after which we were able to connect successfully using SSMS and application also started working.
Reference: Pinal Dave (https://blog.sqlauthority.com)