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

  • Article

 

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)