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.
——————————
ADDITIONAL INFORMATION:
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
——————————
BUTTONS:
OK
——————————
This was a very generic error, so I asked to check the Application Event Log.
Log Name: Application
Source: MSSQLSERVER
Event ID: 17809
Task Category: Logon
Level: Error
Keywords: Classic
User: N/A
Computer: DBSERVER.SomeDomain.in
Description:
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)
11 Comments. Leave new
Could we use admin connection to update configuration ?
Never tried.. Can you please try and let us know?
great.!
Thanks Vivien.
Please assist…..
After i executed this… EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘user connections’, 0 ;
GO
RECONFIGURE;
GO
then restarted the services. Again tried to open through Enterprise Manger it’s not allowed to connect the database, even not able to open new query analyzer. getting the following error.
TITLE: Connect to Server
——————————
Cannot connect to 192.168.202.110.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Dear,
I followed your SQLCMD script and restarted. it’s working. Thanks…:)
exec sp_configure ‘user connections’,0
go
reconfigure with override
go
I am glad to read this.. Thanks for the update.
Super, it done
Perfect!
Hi,
I am trying to open SQLCMD but it is just opening and closing on its own, I cant able to type in anything in it, kindly help.
means? What is the error? Anything interesting in event log?