當前位置 主頁 > 服務器問題 > win服務器問題匯總 > 最大化 縮小

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

    欄目:win服務器問題匯總 時間:2020-01-01 10:44

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

    下一篇:沒有了
青海十一选五开奖数据