Today I was faced with a pretty interesting problem. One of my colleagues decided to make a change on the ERP’s SQL server mid-day. (don’t ask), that change involved going into SQL Management Studio (SMSS), and changing the Maximum Memory Limit to a value that is less than the physical server memory, in order to deal with some performance issue that we were dealing with.
The server had 16Gb of RAM, and the target intended maximum limit was supposed to be 14Gb. without thinking , he made this to be 14 and pressed ok. DOPE!!! Of course, it clearly states the numbers in MB, NOT GB on the memory limits, so the number 14 ended up limiting the Maximum SQL server memory to 14Mb. On Windows 2008 Server R2 with SQL 2008 Server R2, this number might as well be ZERO, because after this change, nothing was accessible anymore: SQL Management Studio, any new logins, and all existing connections were dropped.
Disclaimer: For any SQL experts out there reading this article, there may very well be a better way to have resolved this, but given my knowledge of a little bit of everything, and not specialized in any topic, I could not claim that I am a DBA by any means. I just did what I could to make things work again. If any of you would have a better suggestion for the future, please do not hesitate to mention it in the comments.
This problem quickly became a tangled mess, as everything I have tried failed with errors, similar to the following:
There is insufficient system memory in resource pool ‘Default’ to run this query
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
These may initial point to the direction that the protocol used for the connection is not able to complete the authentication.
In looking around, I found some suggestions, some of which may have contributed to the final solution:
- Some articles suggestion to point to the SQL server instance as ADMIN:<server> to login to SMSS. This did not work for me.
- Some others have suggested to login to the server with osql -U<user> -P<password> -Q ‘exec sp_configure ‘max server memory (MB)’,3500;’ . That didn’t work either, as the memory allocated was too low to even allow that query to run. So, I would receive the “There is insufficient system memory in resource pool ‘Default’ to run this query.
In addition, even the OSQL command would fail about 30 seconds after the SQL service is ran, because, users trying to connect to it, were saturating the memory, and not allowing me to perform the fix.
At this point, I resorted to restart the SQL server in single user mode. There were two methods to do this:
- From the SQL Server Configuration Manager.
- From the command line:
- Open a command prompt, and navigate to your SQL Server directory
sqlservr -f -m
- This will start the SQL server in minimal configuration mode, and in single user mode. It will also give the status of the service to STDOUT.
You have to remember that single-user-mode means that no other user can have a connection to any of the databases. Theoretically, the SMSS should open up without any issues, however, the SQL Agent, as well as the Object Explorer can open up an additional connection, preventing you from doing what you need.
One other thing I noticed when i start the service from the command line, and even though the service was started in single user mode, a ton of people were still attempting to make connections to the database, thereby preventing me from applying the Maximum Memory change query.
The solution that finally allowed me to make the change was the following:
- Before restarting the service in single-user-mode. Disabled the NIC on the server. (Make sure you’re not connected remotely, or you’ll get disconnected)
- Start the service in minimal configuration mode, and single user mode:
sqlservr -f -m
- Adjust the maximum memory limit:
osql -S :servername: -E -Q "exec sp_configure 'max server memory (MB)' \ ,13500 reconfigure with override" Configuration option 'max server memory (MB)' changed from 16 to 13500. \ Run the RECONFIGURE statement to install. osql -S -E -Q "RECONFIGURE"
Make sure to replace the :servername: with your SQL server name, and the 13500 with whatever value you want for your maximum memory. Remember this is in Megabytes!
Now make sure you stop the SqlServr.exe that is running in the command line, and then start up the original SQL service from services.msc (I’m putting the warning regarding this because I was stumped as to why the SQL service was not starting up normally, only to find out that I had forgotten to stop the single user service running within the command line.