sql2019 资源池“internal”没有足够的系统内存来运行此查询。

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

September 26, 2019

Pinal Dave

SQL Tips and Tricks

3 Comments

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

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.

1	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.

1	sp_configure 'max server memory', 12000

1. Run reconfigure command.

1	reconfigure with override

1. Verify that value set in step 4 is visible in the output. Same command as step 3.

1	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.

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

使用 sqlcmd 实用工具 - SQL Server Management Studio (SSMS) | Microsoft Docs