SQL Server誤設置max server memory的處理方法

 更新時間:2019年12月29日 13:58:36   作者:瀟湘隱者   我要評論
這篇文章主要給大家介紹了關于SQL Server誤設置max server memory的處理方法,文中通過示例代碼介紹的非常詳細,對大家學習或者使用SQL Server具有一定的參考學習價值,需要的朋友們下面來一起學習學習吧下

昨天網上一網友說,由于他同事誤將“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.

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對腳本之家的支持。

相關文章

最新評論

福建体育彩票时时彩11