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

本篇文章小编给大家分享一下SQL Server误设置max server memory处理方法，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

在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:Windowssystem32>net stop mssqlserver

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.

C:Windowssystem32>net start mssqlserver

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.