主頁(yè) > 知識(shí)庫(kù) > SQL Server誤設(shè)置max server memory的處理方法

SQL Server誤設(shè)置max server memory的處理方法

昨天網(wǎng)上一網(wǎng)友說(shuō)，由于他同事誤將“max server memory”設(shè)置為10M后，SQL Server數(shù)據(jù)庫(kù)登錄不了，當(dāng)時(shí)我簡(jiǎn)單測(cè)試了一下，今天有空就順手將整個(gè)過(guò)程整理一下，記錄在此。

在SSMS的UI界面設(shè)置“max server memory”，即使你設(shè)置為10M大小，但是它會(huì)“悄悄”默認(rèn)修改為128M，你用Profile跟蹤或者設(shè)置后會(huì)發(fā)現(xiàn)，它偷偷“修改”了你的設(shè)置值（改為了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.

如果你沒(méi)有注意這些細(xì)節(jié)，或者不信這個(gè)事情，那么也可以用腳本測(cè)試一下，如下所示，它提示你這個(gè)值（10M）不是一個(gè)有效值。

當(dāng)你對(duì)“max server memory”做了錯(cuò)誤設(shè)置后，那么基本上，任何查詢或連接都會(huì)出現(xiàn)類似下面這樣的錯(cuò)誤：

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 - 遠(yuǎn)程主機(jī)強(qiáng)迫關(guān)閉了一個(gè)現(xiàn)有的連接。) (Microsoft SQL Server, Error: 10054)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20ServerEvtSrc=MSSQLServerEvtID=10054LinkId=20476

------------------------------
遠(yuǎn)程主機(jī)強(qiáng)迫關(guān)閉了一個(gè)現(xiàn)有的連接。

你檢查數(shù)據(jù)庫(kù)的錯(cuò)誤日志，就會(huì)發(fā)現(xiàn)有很多額外信息，摘抄部分如下：

.........................................................
.........................................................
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
---------------------------------------- ----------
.........................................................
.........................................................

要解決這個(gè)問(wèn)題，你需要關(guān)閉數(shù)據(jù)庫(kù)服務(wù)，然后以單用戶模式+最小配置啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例，然后去修改max server memory參數(shù)。關(guān)閉數(shù)據(jù)庫(kù)過(guò)程中如果遇到一些問(wèn)題，可以通過(guò)重啟服務(wù)器解決問(wèn)題（這個(gè)要根據(jù)具體實(shí)際情況決定，有時(shí)候不會(huì)遇到問(wèn)題，有時(shí)候會(huì)遇到一些問(wèn)題，例如net stop mssqlserver命令卡住，出現(xiàn)service_state[MSSQLSERVER]): Stop pending）

注意：如果以單用戶模式啟動(dòng)，然后以sqlcmd去連接數(shù)據(jù)庫(kù)，就會(huì)出現(xiàn)下面錯(cuò)誤，所以必須以單用戶模式+最小配置啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例

EXEC sys.sp_configure 'max server memory (MB)',4096; #根據(jù)實(shí)際情況設(shè)置內(nèi)存大小。
 
RECONFIGURE
 
GO

然后重啟SQL Server實(shí)例，問(wèn)題就解決了。當(dāng)然你也可以還原master庫(kù)的備份到其它測(cè)試數(shù)據(jù)庫(kù)，然后用還原后master數(shù)據(jù)庫(kù)的相關(guān)文件替換當(dāng)前數(shù)據(jù)庫(kù)master的相關(guān)文件來(lái)解決問(wèn)題。但是那樣會(huì)相對(duì)麻煩，沒(méi)有這種方法簡(jiǎn)便、有效！

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.

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:

SQL Server出現(xiàn)System.OutOfMemoryException異常的解決方法
MySQL Memory 存儲(chǔ)引擎淺析
mysql Out of memory (Needed 16777224 bytes)的錯(cuò)誤解決
mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法
SqlServer如何通過(guò)SQL語(yǔ)句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息
mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法

標(biāo)簽：衡水湖南黃山蘭州仙桃崇左湘潭銅川

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL Server誤設(shè)置max server memory的處理方法》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。