昨天網上一網友說，由於他同事誤將“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'
GORECONFIGURE WITH OVERRIDE
GOConfiguration 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 12019-12-24 10:16:12.70 Server
Process/System Counts Value---------------------------------------- ----------Available Physical Memory 6614454272Available Virtual Memory 140726213148672
Available Paging File 7776440320Working Set 95432704Percent 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 10652776VM Committed 57972
Locked Pages Allocated 86472
Large Pages Allocated 0Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 131072
Current Committed 144448Pages Allocated 84176
Pages Reserved 0
Pages Free 0Pages In Use 144432
Page Alloc Potential -19912
NUMA Growth Phase 2
Last OOM Factor 1Last OS Error 02019-12-24 10:16:12.70 Server
Memory node Id = 0 KB
---------------------------------------- ----------VM Reserved 10652712VM Committed 57952
Locked Pages Allocated 86472
Pages Allocated 84176
Pages Free 0Target Committed 131048
Current Committed 144424Foreign Committed 0Away Committed 0
Taken Away Committed 0
2019-12-24 10:16:12.70 Server
Memory node Id = 64 KB
