Udostępnij za pośrednictwem

Client unable to establish connection.. error when you configure ‘max server memory’ to a low value

  • Artykuł

 

When you inadvertently set SQL Server’s ‘max server memory’ to a very low value, you may experience one or more of the following symptoms:

  • SQL Server cannot be started (from a tool like configuration manager) and the following message is logged in the SQL error log:
    ”An internal query can’t be executed due to inefficient memory…

  • SQL instance is started, but connections would fail with an error message that reads:
    Cannot connect to <Servername>
    Additional information:
    The client was unable to establish connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server, the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider : Shared Memory Provider: Error :0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error:233)

    No process on the other end of the pipe.

 

As noted in this blog, we can try to workaround this issue by starting SQL Server with minimal configuration by adding–f  to SQL startup parameters, but in most cases this causes SQL to start in single user mode and the connection is typically grabbed by some other process other than a tool like SSMS that can be used to correct the memory setting. The solution to this scenario is to specify the app that will be allowed to connect to SQL Server by using the –m parameter as noted below:

NET START MSSQLSERVER /f /m" Microsoft SQL Server Management Studio - Query"

OR

NET START MSSQLSERVER /f /m"SQLCMD"

Additional References:

Server memory server configuration options: https://msdn.microsoft.com/en-us/library/ms178067(v=sql.110).aspx.

How to: Start an instance of SQL Server: https://msdn.microsoft.com/en-us/library/ms180965(v=sql.105).aspx

Database Engine Service Startup Options https://msdn.microsoft.com/en-us/library/ms190737.aspx

 

Contributed by: Idit Izak (Senior Support engineer in SQL team)

Comments

  • Anonymous
    January 10, 2017
    Restarting MSSQLSERVER service does the trick
  • Anonymous
    May 16, 2017
    The way I've fixed was adding the address and the name of the server to the hosts file (Windows\system32\drivers\etc\hosts), I believe the computer is not resolving the name.Try that