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'GORECONFIGURE WITH OVERRIDEGOConfiguration 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)------------------------------ADDITIONAL INFORMATION: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: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476------------------------------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 12019-12-24 10:16:12.70 ServerProcess/System Counts Value---------------------------------------- ----------Available Physical Memory 6614454272Available Virtual Memory 140726213148672Available Paging File 7776440320Working Set 95432704Percent of Committed Memory in WS 100Page Faults 57030System physical memory high 1System physical memory low 0Process physical memory low 1Process virtual memory low 02019-12-24 10:16:12.70 ServerMemory Manager KB---------------------------------------- ----------VM Reserved 10652776VM Committed 57972Locked Pages Allocated 86472Large Pages Allocated 0Emergency Memory 1024Emergency Memory In Use 16Target Committed 131072Current Committed 144448Pages Allocated 84176Pages Reserved 0Pages Free 0Pages In Use 144432Page Alloc Potential -19912NUMA Growth Phase 2Last OOM Factor 1Last OS Error 02019-12-24 10:16:12.70 ServerMemory node Id = 0 KB---------------------------------------- ----------VM Reserved 10652712VM Committed 57952Locked Pages Allocated 86472Pages Allocated 84176Pages Free 0Target Committed 131048Current Committed 144424Foreign Committed 0Away Committed 0Taken Away Committed 02019-12-24 10:16:12.70 ServerMemory node Id = 64 KB---------------------------------------- ----------VM Reserved 0VM Committed 20Locked Pages Allocated 02019-12-24 10:16:12.70 ServerMEMORYCLERK_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.RECONFIGUREGO
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.