您的位置:首页 > 数据库 > > 正文

sqlserver维护计划保存在哪(SQL Server误设置max server memory的处理方法)

更多 时间:2021-10-15 00:15:51 类别:数据库 浏览量:804

sqlserver维护计划保存在哪

SQL Server误设置max server memory的处理方法

昨天网上一网友说,由于他同事误将“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)不是一个有效值。

    sqlserver维护计划保存在哪(SQL Server误设置max server memory的处理方法)

    当你对“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去连接数据库,就会出现下面错误,所以必须以单用户模式+最小配置启动数据库实例

    sqlserver维护计划保存在哪(SQL Server误设置max server memory的处理方法)

    sqlserver维护计划保存在哪(SQL Server误设置max server memory的处理方法)

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

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对开心学习网的支持。

    标签:max server memory