SQL Max Memory Limit Too Low

SQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server access to whatever memory is assigned to the server, which it will try to use at times at the expense of operating system tasks. Best practice is therefore to reduce the memory allocated to the instance to reserve some for the OS, but be careful! If you accidentally enter 10 thinking you’re allocating 10 GB to SQL or simply hit return too early, you’re in for a rude awakening.

The Maximum Server Memory setting is measured in Megabytes. If you allocate too little memory (such as 10 MB), you will bring the SQL instance and its databases to their knees and won’t be able to get back into SQL Management Studio to correct it. If you try, you’ll get the following error message:

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)

Don’t panic! There is a solution that does not involve reinstalling the software. For this scenario, we used a Windows Server 2008 R2 Std server with 16 GB of RAM and two SQL Server instances of SQL Server 2008 R2. One SQL instance was set to a maximum of 8192 MB of memory. The other one we set to 10 MB. (Interestingly, we found that the setting bottomed out at 16 MB though Microsoft says that the minimum setting is 0. Ultimately though, it doesn’t matter. You can’t do any more with 16 MB than you can with 0.)

1. Turn off all SQL services for all instances on the server.
2. Disable TCP/IP for all instances in SQL Server Configuration Manager. (You may not actually need to do this, but we did as an extra precautionary measure to prevent any attempts by other users to even try to hit our service in single user mode.)
3. Open a command prompt and navigate to the folder containing sqlservr.exe for the instance you want to fix. (If User Account Controls are turned on, right-click and run as administrator.)
   

   cd C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn

   

4. Start sqlservr.exe in single user mode using the -f, -m, -d, -e, and -l flags. (For -d, -e, and -l path information, refer to the Properties of the SQL Server service in SQL Server Configuration Manager, on the Advanced tab, in the Startup Parameters field.) It will throw a lot of text back on load. Watch for registry errors, indicating you may have missed something above.
   

   sqlservr -f -m”SQLCMD” -d D:SQLDataMSSQL10_50.MSSQLSERVERMSSQLDATAmaster.mdf -e D:SQLDataMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG -l D:SQLDataMSSQL10_50.MSSQLSERVERMSSQLDATAmastlog.ldf

   

5. Open a second command prompt (again, run as administrator if you need to) and use SQLCMD to change the max memory allocation to what you want it to be in Megabytes. At the end, you should see a message along the lines of: Configuration option ‘max server memory (MB)’ changed from 16 to 4096.
   

   SQLCMD
   sp_configure ‘show advanced options’,1;
   GO
   RECONFIGURE;
   GO
   sp_configure ‘max server memory’,4096;
   GO
   RECONFIGURE;
   GO

6. Close both command prompts to kill the single user mode session.
7. Enable TCP/IP for all instances in SQL Server Configuration Manager if you disabled it.
8. Turn SQL services for all instances back on.
9. Verify functionality and have an energy bite to replenish all of that adrenaline you just burned through averting disaster in mere minutes!