Sometimes, making changes to a system without fully understanding the impact can lead to unexpected consequences. This was the case when one of my clients contacted me in a panic, explaining that they had made a change to their SQL Server settings and now their application was completely down, resulting in significant financial losses.
Upon further investigation, we discovered that the error message displayed was: “Could not connect because the maximum number of user connections has already been reached.” This error prevented any connections to the SQL Server, leaving the application inaccessible.
To resolve this issue, we quickly initiated a desktop sharing session to troubleshoot the problem. We noticed an additional error message when attempting to connect from SQL Server Management Studio (SSMS): “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.)”
Given the generic nature of this error, we decided to check the Application Event Log for more information. There, we found the following message: “Could not connect because the maximum number of user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed.”
Based on this information, we determined that the number of user connections was set to 1, which was causing the issue. To resolve it, we needed to change this value to 0. However, the challenge was that someone was grabbing connections so quickly that we were unable to connect.
Fortunately, I recalled a previous blog post I had written about the single user mode in SQL Server. In that post, I explained how to start SQL Server with the /mSQLCMD parameter, which allows for a single user connection. We decided to follow those steps to gain access to the SQL Server instance in single user mode via SQLCMD.
Once connected, we executed the following command to change the user connections value:
exec sp_configure 'user connections', 0 go reconfigure with override go
After executing these commands, we restarted the SQL Server. As a result, we were able to successfully connect using SSMS, and the application started working again.
It is crucial to exercise caution when making changes to SQL Server settings, especially if the impact is unknown. In situations like this, it is essential to have a thorough understanding of the system and to follow best practices to avoid potential issues.