Oops…I Broke the SQL Server

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.

Setting the Max Memory too low

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

  1. First, you will need to be an administrator on the SQL Server Instance you want to fix.
  2. Now, from the Start | Run menu in Win 7, or Start | Search in Win 10, look for CMD.
    Start Run CMD   Start Search CMD
  3. Now RIGHT-Click on cmd.exe or Command Prompt and select Run As Administrator.
  4. Repeat steps 2 and 3, so you have two command windows open. Like so:
    Two Command Windows
    In the left window, we will start the instance we need to repair.  In the right window, we will connect to that instance with SQLCMD and fix the memory setting.
  5. In the both windows, you need to change to the Binn directory 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.DUMBO folder.  Lastly, the Binn folder is below the instance atInstance\MSSQL\Binn path.  So I enter these two commands:

    D: <enter>

    Then,

    cd D:\Program Files\Microsoft SQL Server\MSSQL12.DUMBO\MSSQL\Binn <enter>

    Change Directory

  6. Now that I am in the Binn folder, I can start the SQL Server Instance.  Note:  This assumes the instance is stopped.  Go to Start | Control Panel | Administrative Tools | Services and find the SQL Server (InstanceName) Service and make sure the Status column is blank.  If it says ‘Started’, then right-click it and Stop the service.
    Administrative Tools
  7. So, back to the command window.  On the left side, we need to start the SQLSERVR service in single user mode, being sure to name the correct instance, like so:

    sqlservr -f -sDUMBO (where DUMBO is the name of the instance to fix)

    You should see SQL Server display a ton of messages.
    SQL Server Running

    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.

  8. 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 (-E parameter) 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)

  9. The SQLCMD prompt 1> should appear:
    SQLCMD Connected
  10. Now enter the following SQL Server commands, pressing <enter> at the end of each line:

    1> sp_configure 'show advanced options', 1;
    2> go
    1> reconfigure;
    2> go
    1> sp_configure 'max server memory', 64000;
    2> go
    1> reconfigure;
    2> go

    Your screen should look like this:
    After the SQL Commands

  11. 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.
  12. At the 1> prompt, type exit and press <enter>.  You can now close the window on the right.
  13. On the left, press Ctrl-C to stop the instance and enter Y when prompted.  You can now close the left window.
  14. Finally, restart the service in the Administrative Tools | Services window by right-clicking it and selecting Start.
  15. The End.

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s