SQL Server误设置max server memory的解决办法

这篇文章主要为大家详细介绍了SQL Server误设置max server memory的简单示例，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

昨天网上一网友说，由于他同事误将“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 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---------------------------------------- ----------..................................................................................................................

要解决这个问题，你需要关闭数据库服务， 然后以单用户模式+最小配置启动数据库实例，然后去修改max server memory参数。 关闭数据库过程中如果遇到一些问题，可以通过重启服务器解决问题（这个要根据具体实际情况决定，有时候不会遇到问题，有时候会遇到一些问题，例如net stop mssqlserver命令卡住，出现service_state[MSSQLSERVER]): Stop pending）

注意：

【图片暂缺】

【图片暂缺】

代码如下：

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.

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对512笔记的支持。

注：关于SQL Server误设置max server memory的简单示例的内容就先介绍到这里，更多相关文章的可以留意