sqlserver实验心得体会_sqlserver 关于DBCC CHECKDB的总结

官方文档

https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-2017

通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性：

对数据库运行 DBCC CHECKALLOC。

对数据库中的每个表和视图运行 DBCC CHECKTABLE。

对数据库运行 DBCC CHECKCATALOG。

验证数据库中每个索引视图的内容。

使用 FILESTREAM 在文件系统中存储 varbinary(max) 数据时，验证表元数据和文件系统目录和文件之间的链接级一致性。

验证数据库中的 Service Broker 数据。

DBCC CHECKDB 其实主要做两件事情：

检查数据库里有没有损坏发生(不检查禁用的索引)

尽力修复数据库损坏，使数据库能够被重新正常访问。

DBCC CHECKDB 最佳实践

建议对生产系统频繁使用 PHYSICAL_ONLY 选项。 使用 PHYSICAL_ONLY 可以极大地缩短对大型数据库运行 DBCC CHECKDB 的运行时间。 同时建议您定期运行没有选项的 DBCC CHECKDB。 应当以什么频率执行这些运行任务将取决于各个企业及其生产环境。

DBCC CHECKDB修复参数

示例：DBCC CHECKDB ('db_name', REPAIR_FAST);

1. REPAIR_ALLOW_DATA_LOSS 尝试修复报告的所有错误。 这些修复可能会导致一些数据丢失。

2. REPAIR_FAST 保留该语法只是为了向后兼容。 未执行修复操作。

3. REPAIR_REBUILD，执行不会丢失数据的修复。 这包括快速修复(如修复非聚集索引中缺少的行)以及更耗时的修复(如重新生成索引)。此参数不修复涉及 FILESTREAM 数据的错误。

DBCC CHECKDB是否加锁

DBCC CHECKDB默认不加锁而是工作在一个隐藏的数据库快照，执行DBCC CheckDB时指定了TABLOCK选项才会加锁

DBCC CHECKDB参数说明

ALL_ERRORMSGS：显示针对每个对象报告的所有错误。 默认情况下显示所有错误消息。

EXTENDED_LOGICAL_CHECKS：如果兼容性级别为 100 (SQL Server 2008) 或更高，则对索引视图、XML 索引和空间索引(如果存在)执行逻辑一致性检查。

NO_INFOMSGS：取消显示所有信息性消息。

NOINDEX：指定不应对用户表的非聚集索引执行会占用很大系统开销的检查。 这将减少总执行时间。 NOINDEX 不影响系统表，因为总是对系统表索引执行完整性检查。

PHYSICAL_ONLY：将检查限制为页和记录标头的物理结构完整性以及数据库的分配一致性。 设计该检查是为了以较小的开销检查数据库的物理一致性，但它还可以检测会危及用户数据安全的残缺页、校验和错误以及常见的硬件故障。因此，使用 PHYSICAL_ONLY 选项可能会大幅减少对较大数据库运行 DBCC CHECKDB 所需的时间，所以对需要频繁检查的生产系统，建议使用此选项。我们仍然建议完整地定期执行 DBCC CHECKDB。

ESTIMATEONLY：显示运行包含所有其他指定选项的 DBCC CHECKDB 时所需的 tempdb 空间估计量。 不执行实际数据库检查。

DATA_PURITY：使 DBCC CHECKDB 检查数据库中是否存在无效或越界的列值。

TABLOCK：使 DBCC CHECKDB 获取锁，而不使用内部数据库快照。 这包括一个短期数据库排他 (X) 锁。 TABLOCK 可使 DBCC CHECKDB 在负荷较重的数据库上运行得更快，但 DBCC CHECKDB 运行时会减少数据库上可获得的并发性。

DBCC

CHECKDB

错误消息

DBCC CHECKDB 命令结束之后，便会将一个消息写入 SQL Server 错误日志。 如果 DBCC 命令成功执行，则消息指示成功以及命令的运行时间。 如果 DBCC 命令在完成检查之前由于错误而停止，则消息将指示命令已终止，并指示状态值和命令运行的时间。 下表列出并说明了此消息中可包含的状态值。

State

描述

0

出现错误号 8930。 这表示元数据中存在的损坏终止了 DBCC 命令。

1

出现错误号 8967。 存在一个内部 DBCC 错误。

2

在紧急模式数据库修复过程中出错。

3

这表示元数据中存在的损坏终止了 DBCC 命令。

4

检测到断言或访问违规。

5

出现终止了 DBCC 命令的未知错误

sp_MSforeachDB

sp_MSforeachdb是微软提供的不公开的存储过程，存储在master数据库中。可以用来对某个数据库的所有表或某个SQL服务器上的所有数据库进行管理，下面将对此进行详细介绍。

sp_MSforeachDB使用DBCC CHECKDB的示例：

use master

exec sp_MSforeachDB 'DBCC CHECKDB ([?]) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY,NO_INFOMSGS'

其中?表示通配符，表示所有数据库

DBCC CHECKDB 遇到的一些错误及分析

1、There is insufficient memory available in the buffer pool. [SQLSTATE 42000] (Error 802)  During undoing of a logged operation in database 'HistoryDB', an error occurred at log record ID (5106285:51843537:99). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database. [SQLSTATE 42000] (Error 3314)  A database snapshot cannot be created because it failed to start.

原因分析：报错很明显：缓冲池中没有足够的可用内存。检查是否数据库HistoryDB特别大导致，如果是则在DBCC CHECKDB时加上PHYSICAL_ONLY 选项

2、Object ID 34 (object 'sys.sysschobjs'):  DBCC could not obtain a lock on this object because the lock request timeout period was exceeded.  This object has been skipped and will not be processed.

原因分析：当我看到这个错误时，我问自己一个问题:“DBCC CHECKDB执行锁吗?”答案是否定的。从SQL Server 2005开始，DBCC CheckDB工作在一个隐藏的数据库快照上。数据库快照是数据库的只读副本。由于快照I/O开销，您可以看到服务器上出现了一些阻塞，或者用户速度较慢，但肯定没有锁。这个时候要检查自己的DBCC CHECKDB是否加了TABLOCK选项，如果是则取消TABLOCK选项