How to change configuration setting when your SQL Server instance cannot start

Recently one of my colleagues asked me for help with his SQL Server instance. He made a mistake while configuring Maximum Server Memory and his instance was not able to run because of wrong memory settings. He set it to low 10MB instead of 10GB. I was working on customer related issues which couldn’t wait, so I asked other DBA to help him with this. Of course everything was fixed in a few minutes. Later I realized that’s good topic for my next blog post.  So it is…

What to do when your SQL Server doesn’t start because of wrong memory settings?

First of all you need to figure out what is the reason that your instance cannot be started. After quick look int the server error log we can find helpful information.

Error: 701, Severity: 17, State: 65.
There is insufficient system memory in resource pool ‘internal’ to run this query.

That is our hint – lack of available memory. Fix is very easy:

  1. Open command prompt. Navigate to SQL Server Binn folder. Start instance with -f startup option. In case you have a named instance you also need to specify -S parameter with instance name.
    c:\Program Files\Microsoft SQL Server\MSSQL12.SS2014\MSSQL\Binn>sqlservr.exe -sSS2014 -f

    Microsoft Doc says:

    -fStarts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.
    -mStarts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started. CHECKPOINT guarantees that completed transactions are regularly written from the disk cache to the database device. (Typically, this option is used if you experience problems with system databases that should be repaired.) Enables the sp_configure allow updates option. By default, allow updates is disabled. Starting SQL Server in single-user mode enables any member of the computer's local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.
    This will start your instance.
  2. As a next step you have to open another command prompt to start SQLCMD utility. SQLCMD is a small and simple command line client for SQL Server. To start it you need to provide your instance name.
    C:\Windows\system32>sqlcmd -S .\ss2014

    After this you can use regular T-SQL commands:

    C:\Windows\system32>sqlcmd -S .\ss2014
    1> EXEC sp_configure
    2> GO
  3. That will allow you to reconfigure Maximum Server Memory setting:
    1> EXEC sp_configure 'show advanced options', 1
    2> GO
    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    1> RECONFIGURE
    2> GO
    1> EXEC sp_configure 'max server memory (MB)', 8192
    2> GO
    

    That should make it! In the worst case you can get such error:

    1> EXEC sp_configure 'max server memory (MB)', 8192
    2> GO
    Msg 701, Level 17, State 130, Server H1234567890\SS2014, Line 1
    There is insufficient system memory in resource pool 'internal' to run this query.

    On the first command prompt with SQL Server running you will see such errors:

    Server      Error: 28709, Severity: 16, State: 19
    Server      Dispatcher was unable to create new thread.
  4. In such case you need to close SQLCMD. To do it type: quit.
    Stop SQL Server instance by pressing Ctrl+C in command prompt. To start it again follow instructions from step #1. Then you can connect again to SQL Server with SQLCMD following instructions from step #2. As a first command run:

    1> EXEC sp_configure 'max server memory (MB)', 8192
    2> GO
    Configuration option 'max server memory (MB)' changed from 128 to 8192. Run the RECONFIGURE statement to install.
    1> RECONFIGURE
    2> GO
    
  5. That’s it! You can quit SQLCMD and stop SQL Server in command prompt. Now you will be able to start it from SQL Server Configuration Manager.

SQL Server Configuration Manager

Wrong settings

Max Server Memory is just one of the many settings which configured wrongly can cause similar troubles. It’s good to remember this method just in case of such issues.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *