SQL SERVER – Unable to Start SQL Server Service or Connect After Incorrectly Setting Max Server Memory to a Low Value

One of my clients contacted me for whom I assisted incomplete installation of SQL Server along with health check and backup automation. Here is the email which came across about max server memory.

Hi Pinal,

Please help! This is urgent as SQL Server is down. This is the one which you set up for us.

I was in SQL Server Management studio and I was going to put the memory allotted to 145000 MB and instead, I clicked OK when it was 14 MB. After that, I was unable to connect. I thought restart would help but now SQL Service is unable to start.

Quick help would be appreciated.

Thanks.
<Name Hidden>

Within 5 minutes I was online with them. Here is the error if you simulate the same scenario by setting SQL memory to the lowest value.

Max Server Memory

SQL SERVER - Unable to Start SQL Server Service or Connect After Incorrectly Setting Max Server Memory to a Low Value mem-too-low-01

Cannot connect to localhost.
——————————
ADDITIONAL INFORMATION:
The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following:  the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
——————————
No process is on the other end of the pipe
——————————

When I looked at ERRORLOG, here were the few lines about Max Server Memory.

  • Error: 701, Severity: 17, State: 65.
  • There is insufficient system memory in resource pool ‘internal’ to run this query.
  • Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
  • Error: 17300, Severity: 16, State: 1.
  • SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option ‘user connections’ to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
  • SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

The out of memory error (701: There is insufficient system memory in resource pool ‘internal’ to run this query.) is clearly side effect of what they have done by mistake. At this point, we need to start SQL and reconfigure memory to the value which they wanted to set.

WORKAROUND/SOLUTION

In this situation where SQL is not starting due to incorrect memory setting, we have a way to start SQL Service in minimal configuration using a parameter called as f. Here are the steps.

If SQL is running and you are not able to connect then first stop SQL Service. You can do it via command prompt, services applet or SQL Server Configuration Manager. There are chances that SQL Service might not stop then you may want to kill respective sqlservr.exe from task manager.

  1. Once SQL is stopped, we need to start with f parameter which stands for minimal configuration. There are multiple ways to do it and I would explain the easiest way.
    1. Open command prompt (Run as Administrator).
    2. For default instance run below:

NET START MSSQLSERVER /f /mSQLCMD

For named instance run below:

NET START MSSQL$INSTANCENAME /f /mSQLCMD

Make sure SQLCMD is in upper case.

SQL Service should start.

  1. Connect to SQL using SQLCMD. Note that SSMS and other tool won’t work as we have allowed connection via only SQLCMD using /m parameter. This is to ensure that the application is not taking connection before us.
  2. Check max server memory using below command.
sp_configure 'max server memory'

you should notice a low value in config_value column.

  1. To reset the value to your desired value (in MB), run below command. I have put value like 12000 which is equal to 12 GB.
sp_configure 'max server memory', 12000
  1. Run reconfigure command.
reconfigure with override
  1. Verify that value set in step 4 is visible in the output. Same command as step 3.
sp_configure 'max server memory'
  1. Once you are satisfied “exit” from the SQLCMD. Now stop SQL Service as its running with special parameters.
  1. For default instance run below:
    NET STOP MSSQLSERVER
  2. For named instance run below:
    NET STOP MSSQL$INSTANCENAME

SQL Service should stop.

  1. Now you are free to start SQL using any method which is easier for you. If its cluster then you need to bring SQL resource online via Failover Cluster Manager. Here is the command to start SQL from the command prompt. This is same as Step 1 but without any extra parameter.
  1. For default instance run below:
    NET START MSSQLSERVER
  2. For named instance run below:
    NET START MSSQL$INSTANCENAME

SQL Service should start.

Here is the pictorial representation of steps.

SQL SERVER - Unable to Start SQL Server Service or Connect After Incorrectly Setting Max Server Memory to a Low Value mem-too-low-02

Hope this article would help someone who is in trouble and wants to fix the issue ASAP. Please provide feedback via the comments section. SQL SERVER –Start Stop Restart SQL Server From Command Prompt

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Script to Get Partition Info Using DMV
Next Post
SQL SERVER – Strange Error Related to Alias

Related Posts

1 Comment. Leave new

  • I have tried this at a dev server for learning purposes and i came accross 2 things. I configured the max server memory via the GUI with 14 MB, closed SSMS and tried to re-connect.. didn’t work. Great, this is what we want for this test.

    1. I start the service as mentioned, connected and used sp_configure to request the max memory values. I see the config_value set to 128 and run_value to 2147483647. I would expect the run_value to apply, not the config_value. Technet says ‘run_value – Currently running value of the configuration option’. So I would assume that the running value is 2147483647, so the service would start.. it doesn’t of course. I have tried to look it up, but cannot find a real clear anwser for this.

    2. Changing the value in SQLCMD and querying the values after the change gives me the following output: ‘Msg 15123, Level 16, State 1, Server , Procedure sp_configure, Line 62 The configuration option ‘max server memory’ does not exist, or it may be an advanced option.’. Turning advanced options on again fixed this. Seems it is somehow changed in the process. Maybe because we use reconfigure with override.

    Reply

Leave a Reply

Menu