SQL SERVER – FIX: Could not connect because the maximum number of 1 user connections has already been reached

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

SQL SERVER - FIX: Could not connect because the maximum number of 1 user connections has already been reached max-conn-01

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.

SQL SERVER – The Story of a Lesser Known Startup Parameter in SQL Server – Guest Post by Balmukund Lakhani

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)

, , ,
Previous Post
SQL SERVER 2016 – Early Thoughts and Observations – Notes from the Field #120
Next Post
SQL SERVER – How to Change SQL Server Collation on Microsoft Azure VM (IaaS)

Related Posts

11 Comments. Leave new

  • Neeraj Mittal
    April 7, 2016 8:30 am

    Could we use admin connection to update configuration ?

    Reply
  • vivien zhang
    May 8, 2017 2:26 pm

    great.!

    Reply
  • 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)

    Reply
  • Dear,

    I followed your SQLCMD script and restarted. it’s working. Thanks…:)

    exec sp_configure ‘user connections’,0
    go
    reconfigure with override
    go

    Reply
  • Super, it done

    Reply
  • 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.

    Reply

Leave a Reply

Menu