So this happened. In an attempt to give my SQL Server Instance access to more memory, I set the Max Memory to zero, expecting that to mean infinite. No luck, SQL Server Management Studio (SSMS) set the max memory to 16MB instead and broke the instance. I could not do anything more in SSMS because the instance did not have enough memory.
Note: The screenshots in this article are from a SQL Server 2014 instance, which has apparently fixed this problem so that the Max Server Memory setting defaults to 128MB when you set it to zero and you can still connect with SSMS at 128MB. In 2012 and prior versions, the setting defaults to 16MB, which is what causes all the trouble.
So I googled for: “accidentally set max server memory to 0”. This turned up a ton of useful links, but since I had to piece my solution together from various posts, I have created this blog entry to hopefully help someone else get to the solution more quickly.
How to Increase SQL Server Max Memory in a Named Instance
- First, you will need to be an administrator on the SQL Server Instance you want to fix.
- Now, from the
Start | Runmenu in Win 7, or
Start | Searchin Win 10, look for
- Now RIGHT-Click on
Command Promptand select
Run As Administrator.
- Repeat steps 2 and 3, so you have two command windows open. Like so:
In the left window, we will start the instance we need to repair. In the right window, we will connect to that instance with
SQLCMDand fix the memory setting.
- In the both windows, you need to change to the
Binndirectory of the SQL Instance that you want to repair. In my case, this instance is on the
D:drive so I have to switch to that first. Also, this instance is found in the
D:\Program Files\Microsoft SQL Server\MSSQL12.DUMBOfolder. Lastly, the
Binnfolder is below the instance at
Instance\MSSQL\Binnpath. So I enter these two commands:
cd D:\Program Files\Microsoft SQL Server\MSSQL12.DUMBO\MSSQL\Binn <enter>
- Now that I am in the
Binnfolder, I can start the SQL Server Instance. Note: This assumes the instance is stopped. Go to
Start | Control Panel | Administrative Tools | Servicesand find the SQL Server (InstanceName) Service and make sure the Status column is blank. If it says ‘Started’, then right-click it and
- So, back to the command window. On the left side, we need to start the
SQLSERVRservice in single user mode, being sure to name the correct instance, like so:
sqlservr -f -sDUMBO(where
DUMBOis the name of the instance to fix)
You should see SQL Server display a ton of messages.
If you get this error message:
SQL Server installation is either corrupt or has been tampered with. Error getting instance id from name.
Then check the instance name and try again.
- Now that SQL Server is running in the left window, go to the right window and start
SQLCMD. We need to use a trusted connection (
-Eparameter) and we need to specify the server so we can pick the right instance. Like so:
sqlcmd -E -sSHQBT0084\DUMBO(where SHQBT0084\DUMBO is the server\instance to repair)
- Now enter the following SQL Server commands, pressing
<enter>at the end of each line:
1> sp_configure 'show advanced options', 1;
1> sp_configure 'max server memory', 64000;
Your screen should look like this:
- Assuming that there were no errors when you ran the reconfigure commands, you have fixed the server. Now we need to clean up a bit. If you did get an error, let me know via comment below.
- At the
<enter>. You can now close the window on the right.
- On the left, press
Ctrl-Cto stop the instance and enter
Ywhen prompted. You can now close the left window.
- Finally, restart the service in the
Administrative Tools | Serviceswindow by right-clicking it and selecting
- The End.