SQL Server误设置max server memory的处理方法
昨天网上一网友说，由于他同事误将“maxservermemory”设置为10M后，SQLServer数据库登录不了，当时我简单测试了一下，今天有空就顺手将整个过程整理一下，记录在此。
在SSMS的UI界面设置“maxservermemory”，即使你设置为10M大小，但是它会“悄悄”默认修改为128M，你用Profile跟踪或者设置后会发现，它偷偷“修改”了你的设置值（改为了128M），
EXECsys.sp_configureN'maxservermemory(MB)',N'128' GO RECONFIGUREWITHOVERRIDE GO Configurationoption'maxservermemory(MB)'changedfrom4096to128.RuntheRECONFIGUREstatementtoinstall.
如果你没有注意这些细节，或者不信这个事情，那么也可以用脚本测试一下，如下所示，它提示你这个值（10M）不是一个有效值。
当你对“maxservermemory”做了错误设置后，那么基本上，任何查询或连接都会出现类似下面这样的错误：
AnexceptionoccurredwhileexecutingaTransact-SQLstatementorbatch.(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Thereisinsufficientsystemmemoryinresourcepool'internal'torunthisquery.(MicrosoftSQLServer,Error:701)
------------------------------
ADDITIONALINFORMATION:
Aconnectionwassuccessfullyestablishedwiththeserver,butthenanerroroccurredduringthepre-loginhandshake.(provider:TCPProvider,error:0-远程主机强迫关闭了一个现有的连接。)(MicrosoftSQLServer,Error:10054)
Forhelp,click:http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
------------------------------
远程主机强迫关闭了一个现有的连接。
你检查数据库的错误日志，就会发现有很多额外信息，摘抄部分如下：
.........................................................
.........................................................
2019-12-2410:15:32.84spid53 Thereisinsufficientsystemmemoryinresourcepool'internal'torunthisquery.
2019-12-2410:15:52.88spid53 Error:18056,Severity:20,State:29.(Params:).Theerrorisprintedintersemodebecausetherewaserrorduringformatting.Tracing,ETW,notificationsetcareskipped.
2019-12-2410:15:55.89Server Error:17300,Severity:16,State:1.(Params:).Theerrorisprintedintersemodebecausetherewaserrorduringformatting.Tracing,ETW,notificationsetcareskipped.
2019-12-2410:16:12.70Server Failedallocatepages:FAIL_PAGE_ALLOCATION1
2019-12-2410:16:12.70Server
Process/SystemCounts Value
--------------------------------------------------
AvailablePhysicalMemory 6614454272
AvailableVirtualMemory 140726213148672
AvailablePagingFile 7776440320
WorkingSet 95432704
PercentofCommittedMemoryinWS 100
PageFaults 57030
Systemphysicalmemoryhigh 1
Systemphysicalmemorylow 0
Processphysicalmemorylow 1
Processvirtualmemorylow 0
2019-12-2410:16:12.70Server
MemoryManager KB
--------------------------------------------------
VMReserved 10652776
VMCommitted 57972
LockedPagesAllocated 86472
LargePagesAllocated 0
EmergencyMemory 1024
EmergencyMemoryInUse 16
TargetCommitted 131072
CurrentCommitted 144448
PagesAllocated 84176
PagesReserved 0
PagesFree 0
PagesInUse 144432
PageAllocPotential -19912
NUMAGrowthPhase 2
LastOOMFactor 1
LastOSError 0
2019-12-2410:16:12.70Server
MemorynodeId=0 KB
--------------------------------------------------
VMReserved 10652712
VMCommitted 57952
LockedPagesAllocated 86472
PagesAllocated 84176
PagesFree 0
TargetCommitted 131048
CurrentCommitted 144424
ForeignCommitted 0
AwayCommitted 0
TakenAwayCommitted 0
2019-12-2410:16:12.70Server
MemorynodeId=64 KB
--------------------------------------------------
VMReserved 0
VMCommitted 20
LockedPagesAllocated 0
2019-12-2410:16:12.70Server
MEMORYCLERK_SQLGENERAL(node0) KB
--------------------------------------------------
.........................................................
.........................................................
要解决这个问题，你需要关闭数据库服务，然后以单用户模式+最小配置启动数据库实例，然后去修改maxservermemory参数。关闭数据库过程中如果遇到一些问题，可以通过重启服务器解决问题（这个要根据具体实际情况决定，有时候不会遇到问题，有时候会遇到一些问题，例如netstopmssqlserver命令卡住，出现service_state[MSSQLSERVER]):Stoppending）
注意：如果以单用户模式启动，然后以sqlcmd去连接数据库，就会出现下面错误，所以必须以单用户模式+最小配置启动数据库实例
EXECsys.sp_configure'maxservermemory(MB)',4096;#根据实际情况设置内存大小。 RECONFIGURE GO
然后重启SQLServer实例，问题就解决了。当然你也可以还原master库的备份到其它测试数据库，然后用还原后master数据库的相关文件替换当前数据库master的相关文件来解决问题。但是那样会相对麻烦，没有这种方法简便、有效！
C:\Windows\system32>netstopmssqlserver
TheSQLServer(MSSQLSERVER)serviceisstopping.
TheSQLServer(MSSQLSERVER)servicewasstoppedsuccessfully.
C:\Windows\system32>netstartmssqlserver
TheSQLServer(MSSQLSERVER)serviceisstarting.
TheSQLServer(MSSQLSERVER)servicewasstartedsuccessfully.
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。