昨天網(wǎng)上一網(wǎng)友說,由于他同事誤將“max server memory”設(shè)置為10M后,SQL Server數(shù)據(jù)庫登錄不了,當(dāng)時我簡單測試了一下,今天有空就順手將整個過程整理一下,記錄在此。
在SSMS的UI界面設(shè)置“max server memory”,即使你設(shè)置為10M大小,但是它會“悄悄”默認(rèn)修改為128M,你用Profile跟蹤或者設(shè)置后會發(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.
如果你沒有注意這些細(xì)節(jié),或者不信這個事情,那么也可以用腳本測試一下,如下所示,它提示你這個值(10M)不是一個有效值。
當(dāng)你對“max server memory”做了錯誤設(shè)置后,那么基本上,任何查詢或連接都會出現(xiàn)類似下面這樣的錯誤:
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)閉了一個現(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)閉了一個現(xiàn)有的連接。
你檢查數(shù)據(jù)庫的錯誤日志,就會發(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
---------------------------------------- ----------
.........................................................
.........................................................
要解決這個問題,你需要關(guān)閉數(shù)據(jù)庫服務(wù), 然后以單用戶模式+最小配置啟動數(shù)據(jù)庫實(shí)例,然后去修改max server memory參數(shù)。 關(guān)閉數(shù)據(jù)庫過程中如果遇到一些問題,可以通過重啟服務(wù)器解決問題(這個要根據(jù)具體實(shí)際情況決定,有時候不會遇到問題,有時候會遇到一些問題,例如net stop mssqlserver命令卡住,出現(xiàn)service_state[MSSQLSERVER]): Stop pending)
注意:如果以單用戶模式啟動,然后以sqlcmd去連接數(shù)據(jù)庫,就會出現(xiàn)下面錯誤,所以必須以單用戶模式+最小配置啟動數(shù)據(jù)庫實(shí)例
EXEC sys.sp_configure 'max server memory (MB)',4096; #根據(jù)實(shí)際情況設(shè)置內(nèi)存大小。
RECONFIGURE
GO
然后重啟SQL Server實(shí)例,問題就解決了。 當(dāng)然你也可以還原master庫的備份到其它測試數(shù)據(jù)庫,然后用還原后master數(shù)據(jù)庫的相關(guān)文件替換當(dāng)前數(shù)據(jù)庫master的相關(guā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é)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,謝謝大家對腳本之家的支持。
您可能感興趣的文章:- SQL Server出現(xiàn)System.OutOfMemoryException異常的解決方法
- MySQL Memory 存儲引擎淺析
- mysql Out of memory (Needed 16777224 bytes)的錯誤解決
- mysqld-nt: Out of memory (Needed 1677720 bytes)解決方法
- SqlServer如何通過SQL語句獲取處理器(CPU)、內(nèi)存(Memory)、磁盤(Disk)以及操作系統(tǒng)相關(guān)信息
- mysql Sort aborted: Out of sort memory, consider increasing server sort buffer size的解決方法