欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server误设置max server memory处理小结

程序员文章站 2022-03-21 17:33:43
昨天网上一网友说,由于他同事误将“max server memory”设置为10M后,SQL Server数据库登录不了,当时我简单测试了一下,今天有空就顺手将整个过程整理一下,记录在此。 在SSMS的UI界面设置“max server memory”,即使你设置为10M大小,但是它会“悄悄”默认修... ......

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