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'
go
reconfigure with override
go
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
---------------------------------------- ----------
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
---------------------------------------- ----------
.........................................................
.........................................................
要解决这个问题，你需要关闭数据库服务， 然后以单用户模式+最小配置启动数据库实例，然后去修改max server memory参数。 关闭数据库过程中如果遇到一些问题，可以通过重启服务器解决问题（这个要根据具体实际情况决定，有时候不会遇到问题，有时候会遇到一些问题，例如net stop mssqlserver命令卡住，出现service_state[mssqlserver]): stop pending）
注意：如果以单用户模式启动，然后以sqlcmd去连接数据库，就会出现下面错误，所以必须以单用户模式+最小配置启动数据库实例
exec sys.sp_configure 'max server memory (mb)',4096; #根据实际情况设置内存大小。
reconfigure
go
然后重启sql server实例，问题就解决了。 当然你也可以还原master库的备份到其它测试数据库，然后用还原后master数据库的相关文件替换当前数据库master的相关文件来解决问题。但是那样会相对麻烦，没有这种方法简便、有效！
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.