Summary of SQL Server Setting max server memory incorrectly

Keywords: SQL Server Database SQL Windows

Yesterday, a netizen on the Internet said that after his colleague mistakenly set "max server memory" to 10M, the SQL Server database could not be logged in. At that time, I simply tested it. Today, I will sort out the whole process and record it here.


Set "max server memory" in the UI of SSMS. Even if you set it to 10M, it will "quietly" change to 128M by default. After you track or set it with Profile, you will find that it secretly "modifies" your setting value (changed to 128M),

EXEC sys.sp_configure N'max server memory (MB)', N'128'
Configuration option 'max server memory (MB)' changed from 4096 to 128. Run the RECONFIGURE statement to install.





If you don't pay attention to these details or don't believe this, you can also test it with a script, as shown below, which prompts you that the value (10M) is not a valid value.





After you set "max server memory" incorrectly, basically, any query or connection will have the following errors:



An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server, Error: 701)
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The remote host forced an existing connection to be closed.) (Microsoft SQL Server, Error: 10054)
For help, click:
The remote host forced an existing connection to be closed.



If you check the error log of the database, you will find a lot of additional information. The excerpt is as follows:





2019-12-24 10:15:32.84 spid53      There is insufficient system memory in resource pool 'internal' to run this query.
2019-12-24 10:15:52.88 spid53      Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-12-24 10:15:55.89 Server      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.
2019-12-24 10:16:12.70 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2019-12-24 10:16:12.70 Server   
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                6614454272
Available Virtual Memory                 140726213148672
Available Paging File                    7776440320
Working Set                                95432704
Percent of Committed Memory in WS               100
Page Faults                                   57030
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       1
Process virtual memory low                        0
2019-12-24 10:16:12.70 Server      
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                10652776
VM Committed                                  57972
Locked Pages Allocated                        86472
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                          16
Target Committed                             131072
Current Committed                            144448
Pages Allocated                               84176
Pages Reserved                                    0
Pages Free                                        0
Pages In Use                                 144432
Page Alloc Potential                         -19912
NUMA Growth Phase                                 2
Last OOM Factor                                   1
Last OS Error                                     0
2019-12-24 10:16:12.70 Server      
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                10652712
VM Committed                                  57952
Locked Pages Allocated                        86472
Pages Allocated                               84176
Pages Free                                        0
Target Committed                             131048
Current Committed                            144424
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2019-12-24 10:16:12.70 Server      
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     20
Locked Pages Allocated                            0
2019-12-24 10:16:12.70 Server      
MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------


To solve this problem, you need to shut down the database service, start the database instance in single user mode + minimum configuration, and then modify the max server memory parameter. In the process of closing the database, if you encounter some problems, you can restart the server to solve the problem (this depends on the actual situation, sometimes you will not encounter problems, sometimes you will encounter some problems, for example, the net stop mssqlserver command is stuck, and the service [MSSqlServer] appears): stop pending)



Note: if you start in single user mode and then connect to the database with sqlcmd, the following error will appear, so you must start the database instance in single user mode + minimum configuration




EXEC sys.sp_configure 'max server memory (MB)',4096;  #Set the memory size according to the actual situation.



Then restart the SQL Server instance, and the problem is solved. Of course, you can restore the backup of the master database to other test databases, and then replace the relevant files of the current master database with the relevant files of the restored master database to solve the problem. But that will be relatively troublesome, there is no such a simple and effective way!




C:\Windows\system32>net stop mssqlserver

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.



C:\Windows\system32>net start mssqlserver

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Posted by mwd2005 on Mon, 23 Dec 2019 19:43:03 -0800