SQL Server误设置max server memory的处理方法

昨天网上一网友说，由于他同事误将“max server memory”设置为10M后，SQL Server数据库登录不了，当时我简单测试了一下，今天有空就顺手将整个过程整理一下，记录在此。

在SSMS的UI界面设置“max server memory”，即使你设置为10M大小，但是它会“悄悄”默认修改为128M，你用Profile跟踪或者设置后会发现，它偷偷“修改”了你的设置值（改为了128M），

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

如果你没有注意这些细节，或者不信这个事情，那么也可以用脚本测试一下，如下所示，它提示你这个值（10M）不是一个有效值。

当你对“max server memory”做了错误设置后，那么基本上，任何查询或连接都会出现类似下面这样的错误：

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 - 远程主机强迫关闭了一个现有的连接。) (Microsoft SQL Server, Error: 10054)
 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
 
------------------------------
远程主机强迫关闭了一个现有的连接。 

你检查数据库的错误日志，就会发现有很多额外信息，摘抄部分如下：

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