DAC重置max server memory

15:44 2014-01-24 08R2,一次通过GUI更改'最大服务器内存(MB)'为16MB,errorlog显示信息如下

2014-01-23 15:49:49.71 spid55      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2014-01-23 15:49:49.88 spid55      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2014-01-23 15:49:49.92 spid55      Configuration option 'max server memory (MB)' changed from 2147483647 to 16. Run the RECONFIGURE statement to install.
2014-01-23 15:49:49.92 spid55      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2014-01-23 15:49:49.96 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2014-01-23 15:49:49.99 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2014-01-23 15:49:49.99 spid55      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2014-01-23 15:49:50.06 spid55      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2014-01-23 15:49:50.06 spid55      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2014-01-23 15:50:26.37 spid55      
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                 1619672
VM Committed                                  44800
AWE Allocated                                     0
Reserved Memory                                1024
Reserved Memory In Use                            0
2014-01-23 15:50:26.39 spid55      
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                 1616856
VM Committed                                  42096
AWE Allocated                                     0
MultiPage Allocator                           14312
SinglePage Allocator                          15944
2014-01-23 15:50:26.40 spid55      
Memory node Id = 32                              KB
---------------------------------------- ----------
VM Reserved                                    1728
VM Committed                                   1672
AWE Allocated                                     0
MultiPage Allocator                            1600
SinglePage Allocator                          15944
2014-01-23 15:50:26.40 spid55      
MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
AWE Allocated                                     0
SM Reserved                                       0
SM Committed                                      0
SinglePage Allocator                           1216
MultiPage Allocator                            3528
2014-01-23 15:50:26.39 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
View Code

执行查询语句报错

2014-01-23 16:47:08.80 spid54      错误: 701，严重性: 17，状态: 130。
2014-01-23 16:47:08.80 spid54      There is insufficient system memory in resource pool 'default' to run this query.
2014-01-23 16:47:08.80 spid55      错误: 701，严重性: 17，状态: 123。
2014-01-23 16:47:08.80 spid55      There is insufficient system memory in resource pool 'default' to run this query.
View Code

第二天在对象资源管理器下连接数据库报错(重启过)--08下想重现错误，没能成功
通过DAC连接重置max server memory，重启数据库服务，正常登录

1 sqlcmd -E -S 127.0.0.1,1434
2 sp_configure 'show advanced options',1
3 go
4 reconfigure with override
5 go
6 sp_configure 'max server memory',1024
7 go
8 reconfigure with override
9 go
View Code


--附DAC连接
1、单用户模式启动数据库
开启一个命令窗口，输入以下命令，并保持窗口打开

1 d:
2 cd D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
3 sqlservr.exe mssqlserver -m

查看返回的日志信息，找到类似下面两行

1 Server is listening on [ 127.0.0.1 <ipv4> 1434] .
2 Dedicated admin connection support was established for listening locally on port 1434.

2、使用DAC连接到数据库
在另一个命令窗口运行

1 sqlcmd -A -d master
2 sqlcmd -E -S 127.0.0.1,1434或
3 sqlcmd -Usa -Ppassword -S 127.0.0.1,1434
posted @ 2014-03-07 14:33  Uest  阅读(2322)  评论(0)  编辑  收藏  举报