YourSQLDba将数据库置于紧急模式的原因浅析
2023-09-14 08:58:21 时间
SERVER 2000 上迁移了一个数据库到SQL SERVER 2008
R2上，暂且用DataBaseName代替迁移的真实的数据库名(后面的资料也会将数据库真实的名字用DataBaseName代替)，迁移过程中也没
有啥问题，配置了YourDataBase，作业“YourSQLDba_FullBackups_And_Maintenance”每天凌晨12点整运
行，结果第一天晚上YourDatabase将数据库DataBaseName置于紧急模式，检查错误日志情况如下：
err Error 2508, Severity 16, level 3 : The In-row data RSVD page count for object "bMaterialIn79", index ID 0, partition ID 4563097419776, alloc unit ID 4563097419776(type In-row data) is incorrect. Run DBCC UPDATEUSAGE. /err err Error 2508, Severity 16, level 3 : The In-row data RSVD page count for object "bMaterialIn93", index ID 0, partition ID 70442742317056, alloc unit ID 70442742317056(type In-row data) is incorrect. Run DBCC UPDATEUSAGE. /err msg DBCC results for LibraryMS. /msg msg Service Broker Msg 9675, State 1: Message Types analyzed: 14. /msg msg Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. /msg msg Service Broker Msg 9667, State 1: Services analyzed: 3. /msg msg Service Broker Msg 9668, State 1: Service Queues analyzed: 3. /msg msg Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. /msg msg Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. /msg msg Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. /msg msg Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. /msg msg DBCC results for sys.sysrscols. /msg msg There are 6701 rows in 63 pages for object "sys.sysrscols". /msg msg DBCC results for sys.sysrowsets. /msg msg There are 819 rows in 12 pages for object "sys.sysrowsets". /msg msg DBCC results for sys.sysallocunits. /msg msg There are 848 rows in 17 pages for object "sys.sysallocunits". /msg ................................................................................. ................................................................................. msg DBCC results for bMaterialIn79. /msg msg There are 1346 rows in 18 pages for object "bMaterialIn79". /msg msg CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn79(object ID 69627341). /msg ............................................................................... ............................................................................... msg DBCC results for bMaterialIn93. /msg msg There are 2162 rows in 23 pages for object "bMaterialIn93". /msg msg CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn93(object ID 1074870946). /msg msg CHECKDB found 0 allocation errors and 2 consistency errors in database DataBaseName. /msg msg DBCC execution completed. If DBCC printed error messages, contact your system administrator. /msg
................................................................... .................................................................. DBCC results for bMaterialIn79.
The In-row data RSVD page count for object "bMaterialIn79", index ID 0, partition ID 4563097419776, alloc unit ID 4563097419776(type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn79(object ID 69627341). ..................................................................... ..................................................................... The In-row data RSVD page count for object "bMaterialIn93", index ID 0, partition ID 70442742317056, alloc unit ID 70442742317056(type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn93(object ID 1074870946).
.................................................................... .................................................................... CHECKDB found 0 allocation errors and 2 consistency errors in database DataBaseName.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn79(index bMaterialIn79, partition 1): USED pages(In-row Data): changed from (21) to (19) pages. RSVD pages(In-row Data): changed from (-107) to (73) pages.
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn79(index PK_bMaterialIn79, partition 1): RSVD pages(In-row Data): changed from (8) to (17) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC UPDATEUSAGE(LibraryMS,"dbo.bMaterialIn93"); DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn93(index bMaterialIn93, partition 1): USED pages(In-row Data): changed from (38) to (24) pages. RSVD pages(In-row Data): changed from (-18) to (49) pages.
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn93(index PK_bMaterialIn93, partition 1): RSVD pages(In-row Data): changed from (16) to (17) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
删除 PostgresSql 数据库 报错：有 N 个其它会话正在使用数据库 的解决方案 遇到的问题 在PostgreSQL 9.2 及以上版本，执行下面的语句： postgres=# drop database dbtest; # 执行删除指定数据库的时候，报以下错误 ERROR: database dbtest is being accessed by other users DETAIL: There is 2 other session using the database. 或者使用 Navicat 等第三方数据库连接工具，删除指定数据库的时候报错，说明此时有两个客户端在连接此数据库，此时不能删除数据库。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
错误现象：
YourSQLDba对数据库做一致性检查时，发现有错误，所以它将该数据库置于紧急模式，具体内容如下所
查看YourSQLDba的具体错误日志信息
![clipboard[4]](http://images.cnitblog.com/blog/73542/201308/10211918-99d2d454cbb54364bc0538f23f411829.png "clipboard[4]")
单击Action字段，看到的内容如下（太多内容，部分省略）
err Error 2508, Severity 16, level 3 : The In-row data RSVD page count for object "bMaterialIn79", index ID 0, partition ID 4563097419776, alloc unit ID 4563097419776(type In-row data) is incorrect. Run DBCC UPDATEUSAGE. /err err Error 2508, Severity 16, level 3 : The In-row data RSVD page count for object "bMaterialIn93", index ID 0, partition ID 70442742317056, alloc unit ID 70442742317056(type In-row data) is incorrect. Run DBCC UPDATEUSAGE. /err msg DBCC results for LibraryMS. /msg msg Service Broker Msg 9675, State 1: Message Types analyzed: 14. /msg msg Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. /msg msg Service Broker Msg 9667, State 1: Services analyzed: 3. /msg msg Service Broker Msg 9668, State 1: Service Queues analyzed: 3. /msg msg Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. /msg msg Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. /msg msg Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. /msg msg Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. /msg msg DBCC results for sys.sysrscols. /msg msg There are 6701 rows in 63 pages for object "sys.sysrscols". /msg msg DBCC results for sys.sysrowsets. /msg msg There are 819 rows in 12 pages for object "sys.sysrowsets". /msg msg DBCC results for sys.sysallocunits. /msg msg There are 848 rows in 17 pages for object "sys.sysallocunits". /msg ................................................................................. ................................................................................. msg DBCC results for bMaterialIn79. /msg msg There are 1346 rows in 18 pages for object "bMaterialIn79". /msg msg CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn79(object ID 69627341). /msg ............................................................................... ............................................................................... msg DBCC results for bMaterialIn93. /msg msg There are 2162 rows in 23 pages for object "bMaterialIn93". /msg msg CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn93(object ID 1074870946). /msg msg CHECKDB found 0 allocation errors and 2 consistency errors in database DataBaseName. /msg msg DBCC execution completed. If DBCC printed error messages, contact your system administrator. /msg
对数据库执行DBCC CHECKDB(DataBaseName)得到的内容跟YourSQLDba的错误日志信息一致
................................................................... .................................................................. DBCC results for bMaterialIn79.
The In-row data RSVD page count for object "bMaterialIn79", index ID 0, partition ID 4563097419776, alloc unit ID 4563097419776(type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn79(object ID 69627341). ..................................................................... ..................................................................... The In-row data RSVD page count for object "bMaterialIn93", index ID 0, partition ID 70442742317056, alloc unit ID 70442742317056(type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table bMaterialIn93(object ID 1074870946).
.................................................................... .................................................................... CHECKDB found 0 allocation errors and 2 consistency errors in database DataBaseName.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
分析原因：
这些错误提示数据页在保留空间中不正确的值,在SQL 2000中，这些错误也有可能是数据条目或者数据页的数目跟索引或表中记录的不一致所导致的。CheckDB 不会修复类似错误，在SQL 2005 中CheckDB只会给出一个警告信息。这不是什么严重的错误，按照提示运行DBCC UPDATEUSAGE行了，这通常发生在SQL 2000升级为2005/2008后，在SQL 2005/2008中一般不会遇到。刚好我这个数据库DataBaseName是从SQL 2000升级到SQL 2008，其实这确实不是一个什么严重错误，但是YourSQLDba意识到了“严重性”将数据库出于紧急模式，不能让系统或用户继续操作该数据库了，那 么接下来用DBCC UPDATEUSAGE 解决该问题。
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn79(index bMaterialIn79, partition 1): USED pages(In-row Data): changed from (21) to (19) pages. RSVD pages(In-row Data): changed from (-107) to (73) pages.
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn79(index PK_bMaterialIn79, partition 1): RSVD pages(In-row Data): changed from (8) to (17) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC UPDATEUSAGE(LibraryMS,"dbo.bMaterialIn93"); DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn93(index bMaterialIn93, partition 1): USED pages(In-row Data): changed from (38) to (24) pages. RSVD pages(In-row Data): changed from (-18) to (49) pages.
DBCC UPDATEUSAGE: Usage counts updated for table bMaterialIn93(index PK_bMaterialIn93, partition 1): RSVD pages(In-row Data): changed from (16) to (17) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
关于DBCC UPDATEUSAGE的MSDN解释如下：
-----------------------------------------------------------------------------------------------------------------------
DBCC UPDATEUSAGE 将针对表或索引中的每个分区更正行、已用页、保留页、叶级页和数据页的计数。如果系统表中没有错误，则 DBCC UPDATEUSAGE 不返回数据。如果发现错误，并对其进行更正，同时没有使用 WITH NO_INFOMSGS，DBCC UPDATEUSAGE 将返回系统表中更新的行和列。
使用 DBCC UPDATEUSAGE 同步空间使用计数器。因为 DBCC UPDATEUSAGE 在大型表或大型数据库中运行可能会需要一些时间，所以通常只在怀疑 sp_spaceused 返回的值不正确时使用。sp_spaceused 在返回表或索引的空间信息之前接受可选参数以运行 DBCC UPDATEUSAGE。
升级数据库在 SQL Server 的早期版本中，用于表和索引行计数以及页计数的值可能不正确。根据 SQL Server 2005 之前的版本创建的数据库可能包含错误的计数。因此，我们建议在升级之后运行 DBCC UPDATEUSAGE，以便更正所有的无效计数。
DBCC CHECKDB 已得到增强，可以检测页计数或行计数变为负值的情况。检测到上述问题后，DBCC CHECKDB 的输出会包含一个警告和一个建议，建议运行 DBCC UPDATEUSAGE 解决该问题。
------------------------------------------------------------------------------------------------------------------------
删除 PostgresSql 数据库 报错：有 N 个其它会话正在使用数据库 的解决方案 遇到的问题 在PostgreSQL 9.2 及以上版本，执行下面的语句： postgres=# drop database dbtest; # 执行删除指定数据库的时候，报以下错误 ERROR: database dbtest is being accessed by other users DETAIL: There is 2 other session using the database. 或者使用 Navicat 等第三方数据库连接工具，删除指定数据库的时候报错，说明此时有两个客户端在连接此数据库，此时不能删除数据库。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
相关文章
- influxdb基本操作_一个数据库只有一个内模式
- 【愚公系列】2023年03月 Java教学课程 105-MySQL数据库（触发器）
- Redis学习之Redis集群模式缺陷及其处理详解数据库
- MySQL（十三）之MySQL事务详解数据库
- sql where 与 having的区别详解数据库
- MySQL数据库实现锁定机制（mysql数据锁定）
- 给 Oracle 数据库添加主键的SQL语句（oracle加主键语句）
- Oracle数据库中的模式特性（oracle中的模式）
- 探索Oracle数据库中的IF语句（oracle如何if）
- 利用MySQL脚本快速创建数据库（mysql脚本创建数据库）
- MySQL列详解，让你在数据库存储和查询中更加灵活高效！（mysql列）
- Oracle 共享模式：提高数据库性能的有效手段（oracle共享模式）
- 使用cmd命令查看MySQL数据库详细信息（cmd查看mysql）
- 归档模式如何手动开启Oracle数据库的归档模式（oracle手动开启）
- Oracle数据库如何进入单用户模式？ 25字（oracle单用户模式）
- MySQL数据库：关键字、索引和优化方案（mysql数据库的关键字）
- MSSQL数据库性能优化指南（mssql的优化）
- 度MySQL与Java联手提升数据库性能（mysqljava长）
- MSSQL数据库注入搜索：安全防护要求（mssql注入搜索注入）
- Oracle数据库超出标准模式的新可能（oracle 其他模式）
- MySQL数据库中如何使用代码创建表（mysql中代码创建表）
- 优化Oracle数据库共享模式设置的技巧（oracle共享模式设置）
- Oracle数据库的几种模式探究（oracle几种模式）
- Oracle专业数据库服务的引领者（oracle主要应用领域）
- Oracle数据库的主动归档模式（oracle主动归档模式）
- Oracle数据库中主键自动递增的实现方法（oracle主键自动递增）
- 和用户Oracle数据库中模式与用户的关系研究（oracle中模式）
- 字符从Oracle数据库中截取左侧字符的方法（oracle中从左截取）
- Oracle数据库禁用归档日志模式（oracle不写归档日志）