DBCC CHECKDB (Transact-SQL)DBCC CHECKDB (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

通过执行下列操作检查指定数据库中所有对象的逻辑和物理完整性:Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

  • 对数据库运行 DBCC CHECKALLOCRuns DBCC CHECKALLOC on the database.
  • 对数据库中的每个表和视图运行 DBCC CHECKTABLERuns DBCC CHECKTABLE on every table and view in the database.
  • 对数据库运行 DBCC CHECKCATALOGRuns DBCC CHECKCATALOG on the database.
  • 验证数据库中每个索引视图的内容。Validates the contents of every indexed view in the database.
  • 使用 FILESTREAM 在文件系统中存储 varbinary(max) 数据时,验证表元数据和文件系统目录和文件之间的链接级一致性 。Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • 验证数据库中的 Service BrokerService Broker 数据。Validates the Service BrokerService Broker data in the database.

这意味着不必从 DBCC CHECKDB 单独运行 DBCC CHECKALLOC、DBCC CHECKTABLE 或 DBCC CHECKCATALOG 命令。This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. 有关这些命令执行的检查的详细信息,请参阅这些命令的说明。For more detailed information about the checks that these commands perform, see the descriptions of these commands.

备注

DBCC CHECKDB 在包含内存优化表的数据库上受支持,但验证仅在基于磁盘的表上发生。DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. 但是,作为数据库备份和恢复的一部分,将对内存优化文件组中的文件完成 CHECKSUM 验证。However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.

由于 DBCC 修复选项不可用于内存优化表,您必须定期备份数据库并测试备份。Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. 如果内存优化表中出现数据完整性问题,必须从上次已知的正确备份中还原。If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

DBCC CHECKDB     
    [ ( database_name | database_id | 0    
        [ , NOINDEX     
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]    
    ) ]    
    [ WITH     
        {    
            [ ALL_ERRORMSGS ]    
            [ , EXTENDED_LOGICAL_CHECKS ]     
            [ , NO_INFOMSGS ]    
            [ , TABLOCK ]    
            [ , ESTIMATEONLY ]    
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]    
            [ , MAXDOP  = number_of_processors ]    
        }    
    ]    
]    

参数Arguments

database_name | database_id | 0database_name | database_id | 0
要为其运行完整性检查的数据库的名称或 ID。Is the name or ID of the database for which to run integrity checks. 如果未指定,或者指定为 0,则使用当前数据库。If not specified, or if 0 is specified, the current database is used. 数据库名称必须符合标识符规则。Database names must comply with the rules for identifiers.

NOINDEXNOINDEX
指定不应对用户表的非聚集索引执行会占用很大系统开销的检查。Specifies that intensive checks of nonclustered indexes for user tables should not be performed. 这将减少总执行时间。This decreases the overall execution time. NOINDEX 不影响系统表,因为总是对系统表索引执行完整性检查。NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDREPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
指定 DBCC CHECKDB 修复发现的错误。Specifies that DBCC CHECKDB repair the found errors. 仅将 REPAIR 选项作为最后手段使用。Use the REPAIR options only as a last resort. 指定的数据库必须处于单用户模式,才能使用以下修复选项之一。The specified database must be in single-user mode to use one of the following repair options.

REPAIR_ALLOW_DATA_LOSSREPAIR_ALLOW_DATA_LOSS
尝试修复报告的所有错误。Tries to repair all reported errors. 这些修复可能会导致一些数据丢失。These repairs can cause some data loss.

警告

REPAIR_ALLOW_DATA_LOSS 选项是受支持的功能,但是,它可能并非总是使数据库处于物理上一致的状态的最佳选项。The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. 如果成功,REPAIR_ALLOW_DATA_LOSS 选项可能会导致一些数据丢失。If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. 实际上,它可能导致的数据丢失多于用户从上次已知成功备份还原数据库导致的数据丢失。In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.

MicrosoftMicrosoft 始终建议用户将从上次已知成功备份还原作为从由 DBCC CHECKDB 报告的错误恢复的主要方法。always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. REPAIR_ALLOW_DATA_LOSS 选项不是从已知成功备份还原的替代方法。The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup. 这是一个紧急选项,仅当不可从备份恢复时建议作为“最后手段”使用。It is an emergency "last resort" option recommended for use only if restoring from a backup is not possible.

仅能使用 REPAIR_ALLOW_DATA_LOSS 选项修复的某些错误可能涉及释放行、页或一些列页以清除错误。Certain errors, that can only be repaired using the REPAIR_ALLOW_DATA_LOSS option, may involve deallocating a row, page, or series of pages to clear the errors. 用户不可再访问或恢复已释放的数据,且无法确定已释放数据的准确内容。Any deallocated data is no longer accessible or recoverable for the user, and the exact contents of the deallocated data cannot be determined. 因此,释放任何行或页后参照完整性可能不准确,因为此修复操作不包括检查或维护外键约束。Therefore, referential integrity may not be accurate after any rows or pages are deallocated because foreign key constraints are not checked or maintained as part of this repair operation. 使用 REPAIR_ALLOW_DATA_LOSS 选项后,用户必须检查其数据库的参考完整性(使用 DBCC CHECKCONSTRAINTS)。The user must inspect the referential integrity of their database (using DBCC CHECKCONSTRAINTS) after using the REPAIR_ALLOW_DATA_LOSS option.

在执行修复之前,请创建属于此数据库的文件的物理副本。Before performing the repair, create physical copies of the files that belong to this database. 这包括主数据文件 (.mdf)、任意辅助数据文件 (.ndf)、所有事务日志文件 (.ldf) 和构成数据库的其他容器,包括全文目录、文件流文件夹、内存优化数据等。This includes the primary data file (.mdf), any secondary data files (.ndf), all transaction log files (.ldf), and other containers that form the database including full text catalogs, file stream folders, memory optimized data, etc.

在执行修复前,请考虑将数据库的状态更改为紧急模式,并尝试从关键表中提取尽可能多的信息并保存这些数据。Before performing the repair, consider changing the state of the database to EMERGENCY mode and trying to extract as much information possible from the critical tables and save that data.

REPAIR_FASTREPAIR_FAST
保留该语法只是为了向后兼容。Maintains syntax for backward compatibility only. 未执行修复操作。No repair actions are performed.

REPAIR_REBUILDREPAIR_REBUILD
执行不会丢失数据的修复。Performs repairs that have no possibility of data loss. 这包括快速修复(如修复非聚集索引中缺少的行)以及更耗时的修复(如重新生成索引)。This can include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index.
此参数不修复涉及 FILESTREAM 数据的错误。This argument does not repair errors involving FILESTREAM data.

重要

由于具有 REPAIR 选项的 DBCC CHECKDB 被完全记录且可恢复,MicrosoftMicrosoft 始终建议用户在事务中使用具有 REPAIR 选项的 CHECKDB(运行命令前执行 BEGIN TRANSACTION),这样用户可确认其是否愿意接受操作的结果。Since DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, MicrosoftMicrosoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm he/she wants to accept the results of the operation. 然后用户可执行 COMMIT TRANSACTION 来提交修复操作完成的所有工作。Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. 如果用户不想接受操作的结果,他/她可执行 ROLLBACK TRANSACTION 以撤消修复操作的影响。If the user does not want to accept the results of the operation, he/she can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.    

若要修复错误,建议您通过备份进行还原。To repair errors, we recommend restoring from a backup. 修复操作不会考虑表本身或表之间可能存在的任何约束。Repair operations do not consider any of the constraints that may exist on or between tables. 如果指定的表与一个或多个约束有关,建议您在修复操作后运行 DBCC CHECKCONSTRAINTS。If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. 如果必须使用 REPAIR,则运行不带有修复选项的 DBCC CHECKDB 来查找要使用的修复级别。If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. 如果使用 REPAIR_ALLOW_DATA_LOSS 级别,则建议您在运行带有此选项的 DBCC CHECKDB 之前备份数据库。If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

ALL_ERRORMSGSALL_ERRORMSGS
显示针对每个对象报告的所有错误。Displays all reported errors per object. 默认情况下显示所有错误消息。All error messages are displayed by default. 指定或省略此选项都不起作用。Specifying or omitting this option has no effect. 按对象 ID 对错误消息排序,从 tempdb 数据库生成的那些消息除外。Error messages are sorted by object ID, except for those messages generated from tempdb database.    

EXTENDED_LOGICAL_CHECKSEXTENDED_LOGICAL_CHECKS
如果兼容性级别为 100 (SQL Server 2008SQL Server 2008) 或更高,则对索引视图、XML 索引和空间索引(如果存在)执行逻辑一致性检查。If the compatibility level is 100 ( SQL Server 2008SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.
有关详细信息,请参阅本主题后面备注部分中的“对索引执行逻辑一致性检查” 。For more information, see Performing Logical Consistency Checks on Indexes, in the Remarks section later in this topic.

NO_INFOMSGSNO_INFOMSGS
取消显示所有信息性消息。Suppresses all informational messages.

TABLOCKTABLOCK
使 DBCC CHECKDB 获取锁,而不使用内部数据库快照。Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. 这包括一个短期数据库排他 (X) 锁。This includes a short-term exclusive (X) lock on the database. TABLOCK 可使 DBCC CHECKDB 在负荷较重的数据库上运行得更快,但 DBCC CHECKDB 运行时会减少数据库上可获得的并发性。TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running.

重要

TABLOCK 限制执行的检查;DBCC CHECKCATALOG 未对数据库运行并且 Service BrokerService Broker 数据未进行验证。TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service BrokerService Broker data is not validated.

ESTIMATEONLYESTIMATEONLY
显示运行包含所有其他指定选项的 DBCC CHECKDB 时所需的 tempdb 空间估计量。Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. 不执行实际数据库检查。The actual database check is not performed.

PHYSICAL_ONLYPHYSICAL_ONLY
将检查限制为页和记录标头的物理结构完整性以及数据库的分配一致性。Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. 设计该检查是为了以较小的开销检查数据库的物理一致性,但它还可以检测会危及用户数据安全的残缺页、校验和错误以及常见的硬件故障。This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.
DBCC CHECKDB 完成运行所需的时间可能比早期版本要长得多。A full run of DBCC CHECKDB may take considerably longer to complete than earlier versions. 出现此现象的原因是:This behavior occurs because:

  • 逻辑检查更加全面。The logical checks are more comprehensive.
  • 要检查的某些基础结构更为复杂。Some of the underlying structures to be checked are more complex.
  • 引入了许多新的检查以包含新增功能。Many new checks have been introduced to include the new features.
    因此,使用 PHYSICAL_ONLY 选项可能会大幅减少对较大数据库运行 DBCC CHECKDB 所需的时间,所以对需要频繁检查的生产系统,建议使用此选项。Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. 我们仍然建议完整地定期执行 DBCC CHECKDB。We still recommend that a full run of DBCC CHECKDB be performed periodically. 这些运行的执行频率取决于各业务和生产环境特定的因素。The frequency of these runs depends on factors specific to individual businesses and production environments.
    此参数始终表示 NO_INFOMSGS,不能与任何一个修复选项一同使用。This argment always implies NO_INFOMSGS and is not allowed with any one of the repair options.

警告

指定 PHYSICAL_ONLY 会使 DBCC CHECKDB 跳过对 FILESTREAM 数据的所有检查。Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.

DATA_PURITYDATA_PURITY
使 DBCC CHECKDB 检查数据库中是否存在无效或越界的列值。Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. 例如,DBCC CHECKDB 检测日期和时间值大于或小于 datetime 数据类型的可接受范围的列,或者小数位数或精度值无效的 decimal 或近似 numeric 数据类型列 。For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.
默认情况下将启用列值完整性检查,并且不需要使用 DATA_PURITY 选项。Column-value integrity checks are enabled by default and do not require the DATA_PURITY option. 对于从 SQL ServerSQL Server 的早期版本升级的数据库,默认情况下不启用列值检查,直到 DBCC CHECKDB WITH DATA_PURITY 已在数据库中正确运行为止。For databases upgraded from earlier versions of SQL ServerSQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. 然后,DBCC CHECKDB 将默认检查列值完整性。After this, DBCC CHECKDB checks column-value integrity by default. 有关从 SQL ServerSQL Server 的早期版本升级数据库会对 CHECKDB 有何影响的详细信息,请参阅本主题的“备注”部分。For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL ServerSQL Server, see the Remarks section later in this topic.

警告

如果指定了 PHYSICAL_ONLY,则不执行列完整性检查。If PHYSICAL_ONLY is specified, column-integrity checks are not performed.

无法使用 DBCC 修复选项来纠正该选项所报告的验证错误。Validation errors reported by this option cannot be fixed by using DBCC repair options. 若要了解如何手动更正这些错误,请参阅知识库文章 923247:解决 SQL Server 2005 及更高版本中的 DBCC 错误 2570For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions.

MAXDOPMAXDOP
适用对象SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 到 SQL ServerSQL Server)。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 through SQL ServerSQL Server).

对于语句,替代 sp_configure 的“max degree of parallelism”配置选项 。Overrides the max degree of parallelism configuration option of sp_configure for the statement. MAXDOP 可以超出使用 sp_configure 配置的值。The MAXDOP can exceed the value configured with sp_configure. 如果 MAXDOP 超出使用资源调控器配置的值,则 SQL Server 数据库引擎SQL Server Database Engine 会使用资源调控器 MAXDOP 值(如 ALTER WORKLOAD GROUP 中所述)。If MAXDOP exceeds the value configured with Resource Governor, the SQL Server 数据库引擎SQL Server Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP. 当使用 MAXDOP 查询提示时,所有和 max degree of parallelism 配置选项一起使用的语义规则均适用。All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option.

警告

如果 MAXDOP 设置为零,则 SQL Server 需选择要使用的最大并行度。If MAXDOP is set to zero then SQL Server chooses the max degree of parallelism to use.    

RemarksRemarks

DBCC CHECKDB 不检查禁用的索引。DBCC CHECKDB does not examine disabled indexes. 有关禁用的索引的详细信息,请参阅禁用索引和约束For more information about disabled indexes, see Disable Indexes and Constraints.

如果用户定义类型标记为按字节排序,则该用户定义类型必须只有一个序列化。If a user-defined type is marked as being byte ordered, there must only be one serialization of the user-defined type. 在 DBCC CHECKDB 运行期间,如果按字节排序的用户定义类型没有一致的序列化,则会导致错误 2537。Not having a consistent serialization of byte-ordered user-defined types causes error 2537 when DBCC CHECKDB is run. 有关详细信息,请参阅用户定义类型的要求For more information, see User-Defined Type Requirements.

由于只能以单用户模式修改资源数据库,因此不能直接对其运行 DBCC CHECKDB 命令。Because the Resource database is modifiable only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. 但是,当对 master 数据库执行 DBCC CHECKDB 时,也在内部对资源数据库运行另一个 CHECKDB。However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. 这意味着 DBCC CHECKDB 可能会返回额外的结果。This means that DBCC CHECKDB can return extra results. 如果未设置任何选项,或设置的是 PHYSICAL_ONLYESTIMATEONLY 选项,此命令返回额外结果集。The command returns extra result sets when no options are set, or when either the PHYSICAL_ONLY or ESTIMATEONLY option is set.

SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2 开始,执行 DBCC CHECKDB 不再清除 SQL ServerSQL Server 实例的计划缓存 。Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2, executing DBCC CHECKDB no longer clears the plan cache for the instance of SQL ServerSQL Server. SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2 以前,执行 DBCC CHECKDB 会清除计划缓存。Before SQL Server 2005 (9.x)SQL Server 2005 (9.x) SP2, executing DBCC CHECKDB clears the plan cache. 清除计划缓存将导致对所有后续执行计划进行重新编译,并可能会导致查询性能暂时性地突然降低。Clearing the plan cache causes recompilation of all later execution plans and may cause a sudden, temporary decrease in query performance.

对索引执行逻辑一致性检查Performing Logical Consistency Checks on Indexes

对索引进行的逻辑一致性检查因数据库兼容级别而异,如下所示:Logical consistency checking on indexes varies according to the compatibility level of the database, as follows:

  • 如果兼容性级别为 100 (SQL Server 2008SQL Server 2008) 或更高:If the compatibility level is 100 (SQL Server 2008SQL Server 2008) or higher:
  • 除非指定 NOINDEX,否则 DBCC CHECKDB 对一个表及其所有非聚集索引同时执行物理和逻辑一致性检查。Unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. 但是,在默认情况下,仅对 XML 索引、空间索引和索引视图执行物理一致性检查。However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.
  • 如果指定了 WITH EXTENDED_LOGICAL_CHECKS,便会对索引视图、XML 索引和空间索引(若有)执行逻辑检查。If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, where present. 默认情况下,先执行物理一致性检查,然后执行逻辑一致性检查。By default, physical consistency checks are performed before the logical consistency checks. 如果还指定了 NOINDEX,则仅执行逻辑检查。If NOINDEX is also specified, only the logical checks are performed.

这些逻辑一致性检查可对索引对象的内部索引表及其引用的用户表进行交叉检查。These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. 为了查找外部行,将构造内部查询来对内部表和用户表的完整交集执行查询。To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. 运行此查询可能会对性能产生很大影响,并且无法跟踪其进度。Running this query can have a very high effect on performance, and its progress cannot be tracked. 因此,建议仅在以下情况下才指定 WITH EXTENDED_LOGICAL_CHECKS:怀疑存在与物理损坏无关的索引问题,或已禁用页级别校验和且怀疑存在列级别硬件损坏。Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.

  • 如果索引为筛选索引,DBCC CHECKDB 将执行一致性检查以验证索引项是否满足筛选谓词的要求。If the index is a filtered index, DBCC CHECKDB performs consistency checks to verify that the index entries satisfy the filter predicate.
  • 如果兼容性级别不高于 90,那么除非指定 NOINDEX,否则 DBCC CHECKDB 对一个表或索引视图及其所有非聚集索引和 XML 索引同时执行物理和逻辑一致性检查。If the compatibility level is 90 or less, unless NOINDEX is specified, DBCC CHECKDB performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. 不支持空间索引。Spatial indexes are not supported.
  • 从 SQL Server 2016 开始,不再默认对持久化计算列、UDT 列和筛选索引运行其他检查,以避免昂贵的表达式计算。Starting with SQL Server 2016, additional checks on persisted computed columns, UDT columns, and filtered indexes will not run by default to avoid the expensive expression evaluations. 此更改显著减少了针对包含这些对象的数据库的 CHECKDB 持续时间。This change greatly reduces the duration of CHECKDB against databases containing these objects. 但是,始终会完成这些对象的物理一致性检查。However, the physical consistency checks of these objects is always completed. 仅当指定了 EXTENDED_LOGICAL_CHECKS 选项时,才会在 EXTENDED_LOGICAL_CHECKS 选项中已包含的逻辑检查(索引视图、XML 索引和空间索引)之外,还执行表达式计算。Only when EXTENDED_LOGICAL_CHECKS option is specified will the expression evaluations be performed in addition to already present logical checks (indexed view, XML indexes, and spatial indexes) as part of the EXTENDED_LOGICAL_CHECKS option.

了解数据库的兼容性级别To learn the compatibility level of a database

内部数据库快照Internal Database Snapshot

对于执行这些检查所需要的事务一致性,DBCC CHECKDB 使用内部数据库快照。DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. 这样可以防止在执行这些命令时出现阻塞和并发问题。This prevents blocking and concurrency problems when these commands are executed. 有关详细信息,请参阅查看数据库快照的稀疏文件大小 (Transact-SQL) 以及 DBCC (Transact-SQL) 中的“DBCC 内部数据库快照使用情况”部分。For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL). 如果无法创建快照或指定了 TABLOCK,DBCC CHECKDB 将获取锁以获得所需一致性。If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. 在这种情况下,需要排他数据库锁才能执行分配检查,需要共享表锁才能执行表检查。In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks. 如果无法创建内部数据库快照,则对 master 运行 DBCC CHECKDB 时会失败。DBCC CHECKDB fails when run against master if an internal database snapshot cannot be created. 对 tempdb 运行 DBCC CHECKDB 不会执行任何分配或目录检查,并且必须获取共享表锁才能执行表检查。Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. 这是因为,为了提高性能,不允许对 tempdb 使用数据库快照。This is because, for performance reasons, database snapshots are not available on tempdb. 这意味着,无法获得所需的事务一致性。This means that the required transactional consistency cannot be obtained. 在 Microsoft SQL Server 2012 或更早版本中,针对有文件位于 ReFS 格式化卷上的数据库运行 DBCC CHECKDB 命令时,可能会遇到错误消息。In Microsoft SQL Server 2012 or an earlier version of SQL Server, you may encounter error messages when you run the DBCC CHECKDB command for a database that has its files located on an ReFS-formatted volume. 有关详细信息,请参阅知识库文章 2974455:SQL Server 数据库位于 ReFS 卷上时的 DBCC CHECKDB 行为For more information, see Knowledge Base article 2974455: DBCC CHECKDB behavior when the SQL Server database is located on an ReFS volume.

检查和修复 FILESTREAM 数据Checking and Repairing FILESTREAM Data

对数据库和表启用 FILESTREAM 后,便可选择将 varbinary(max) 二进制大型对象 (BLOB) 存储在文件系统中 。When FILESTREAM is enabled for a database and table, you can optionally store varbinary(max) binary large objects (BLOBs) in the file system. 对在文件系统中存储 BLOB 的数据库使用 DBCC CHECKDB 时,DBCC 将检查文件系统和数据库之间的链接级一致性。When using DBCC CHECKDB on a database that stores BLOBs in the file system, DBCC checks link-level consistency between the file system and database. 例如,如果表包含使用 FILESTREAM 属性的 varbinary(max) 列,则 DBCC CHECKDB 会检查文件系统目录和文件与表行、表列和列值之间是否存在一对一映射 。For example, if a table contains a varbinary(max) column that uses the FILESTREAM attribute, DBCC CHECKDB will check that there is a one-to-one mapping between file system directories and files and table rows, columns, and column values. 如果指定 REPAIR_ALLOW_DATA_LOSS 选项,DBCC CHECKDB 便可修复损坏。DBCC CHECKDB can repair corruption if you specify the REPAIR_ALLOW_DATA_LOSS option. 为修复 FILESTREAM 损坏,DBCC 将删除缺少文件系统数据的任何表行。To repair FILESTREAM corruption, DBCC will delete any table rows that are missing file system data.

最佳实践Best Practices

建议对生产系统频繁使用 PHYSICAL_ONLY 选项。We recommend that you use the PHYSICAL_ONLY option for frequent use on production systems. 使用 PHYSICAL_ONLY 可以极大地缩短对大型数据库运行 DBCC CHECKDB 的运行时间。Using PHYSICAL_ONLY can greatly shorten run-time for DBCC CHECKDB on large databases. 同时建议您定期运行没有选项的 DBCC CHECKDB。We also recommend that you periodically run DBCC CHECKDB with no options. 应当以什么频率执行这些运行任务将取决于各个企业及其生产环境。How frequently you should perform these runs depends on individual businesses and their production environments.

并行检查对象Checking Objects in Parallel

默认情况下,DBCC CHECKDB 对对象执行并行检查。By default, DBCC CHECKDB performs parallel checking of objects. 并行度由查询处理器自动确定。The degree of parallelism is automatically determined by the query processor. 最大并行度的配置与配置并行查询相同。The maximum degree of parallelism is configured just like parallel queries. 若要限制 DBCC 检查可使用的处理器的最大数目,请使用 sp_configureTo restrict the maximum number of processors available for DBCC checking, use sp_configure. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option. 通过使用跟踪标志 2528 可以禁用并行检查。Parallel checking can be disabled by using trace flag 2528. 有关详细信息,请参阅跟踪标志 (Transact-SQL)For more information, see Trace Flags (Transact-SQL).

备注

并非在 SQL ServerSQL Server的每个版本中均提供此功能。This feature is not available in every edition of SQL ServerSQL Server. 有关详细信息,请参阅 SQL Server 2016 的各版本支持的功能的“RDBMS 可管理性”部分中的并行一致性检查。For more information, see parallel consistency check in the RDBMS Manageability section of Features Supported by the Editions of SQL Server 2016.

了解 DBCC 错误消息Understanding DBCC Error Messages

DBCC CHECKDB 命令结束之后,便会将一个消息写入 SQL ServerSQL Server 错误日志。After the DBCC CHECKDB command finishes, a message is written to the SQL ServerSQL Server error log. 如果 DBCC 命令成功执行,则消息指示成功以及命令的运行时间。If the DBCC command successfully executes, the message indicates success and the amount of time that the command ran. 如果 DBCC 命令在完成检查之前由于错误而停止,则消息将指示命令已终止,并指示状态值和命令运行的时间。If the DBCC command stops before completing the check because of an error, the message indicates that the command was terminated, a state value, and the amount of time the command ran. 下表列出并说明了此消息中可包含的状态值。The following table lists and describes the state values that can be included in the message.

StateState 描述Description
00 出现错误号 8930。Error number 8930 was raised. 这表示元数据中存在的损坏终止了 DBCC 命令。This indicates a corruption in metadata that terminated the DBCC command.
11 出现错误号 8967。Error number 8967 was raised. 存在一个内部 DBCC 错误。There was an internal DBCC error.
22 在紧急模式数据库修复过程中出错。A failure occurred during emergency mode database repair.
33 这表示元数据中存在的损坏终止了 DBCC 命令。This indicates a corruption in metadata that terminated the DBCC command.
44 检测到断言或访问违规。An assert or access violation was detected.
55 出现终止了 DBCC 命令的未知错误。An unknown error occurred that terminated the DBCC command.

错误报告Error Reporting

一旦 DBCC CHECKDB 检测到损坏错误,便会在 SQL ServerSQL Server LOG 目录中创建转储文件 (SQLDUMP*nnnn*.txt)。A dump file (SQLDUMP*nnnn*.txt) is created in the SQL ServerSQL Server LOG directory whenever DBCC CHECKDB detects a corruption error. 如果为 SQL ServerSQL Server 实例启用了“功能使用情况数据收集”和“错误报告”功能,该文件将被自动转发给 MicrosoftMicrosoftWhen the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL ServerSQL Server, the file is automatically forwarded to MicrosoftMicrosoft. 收集的数据将用于改进 SQL ServerSQL Server 功能。The collected data is used to improve SQL ServerSQL Server functionality. 转储文件包含 DBCC CHECKDB 命令的结果以及其他诊断输出数据。The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. 只有 SQL ServerSQL Server 服务帐户和 sysadmin 角色的成员有权进行访问。Access is limited to the SQL ServerSQL Server service account and members of the sysadmin role. 默认情况下,sysadmin 角色包含 Windows BUILTIN\Administrators 组和本地管理员组的所有成员。By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. 如果数据收集进程失败,DBCC 命令不会失败。The DBCC command does not fail if the data collection process fails.

纠正错误Resolving Errors

如果 DBCC CHECKDB 报告了任何错误,建议从数据库备份还原数据库,而不运行具有一个 REPAIR 选项的 REPAIR。If any errors are reported by DBCC CHECKDB, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. 如果不存在备份,则运行修复将更正报告的错误。If no backup exists, running repair corrects the errors reported. 要使用的修复选项在报告的错误的末尾处指定。The repair option to use is specified at the end of the list of reported errors. 但是,通过使用 REPAIR_ALLOW_DATA_LOSS 选项来更正错误可能需要删除某些页,因此会删除某些数据。However, correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require deleting some pages, and therefore some data.

在某些情况下,可能会在数据库中输入对列的数据类型而言无效或越界的值。Under some circumstances, values might be entered into the database that are not valid or out-of-range based on the data type of the column. DBCC CHECKDB 可以检测到对所有列数据类型均无效的列值。DBCC CHECKDB can detect column values that are not valid for all column data types. 因此,对从 SQL ServerSQL Server 的早期版本升级的数据库运行带 DATA_PURITY 选项的 DBCC CHECKDB 可能会显示预先存在的列值错误。Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL ServerSQL Server might reveal preexisting column-value errors. 因为 SQL ServerSQL Server 不会自动修复这些错误,所以必须手动更新这些列值。Because SQL ServerSQL Server cannot automatically repair these errors, the column value must be manually updated. 如果 CHECKDB 检测到此类错误,CHECKDB 将返回一个警告、错误号 2570 以及标识受影响的行和手动更正错误的信息。If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error.

修复操作可以在用户事务下执行,以使用户回滚已做的更改。The repair can be performed under a user transaction to let the user roll back the changes that were made. 如果回滚修复,数据库仍会包含错误,因而必须通过备份进行还原。If repairs are rolled back, the database will still contain errors and must be restored from a backup. 修复完成后,备份数据库。After repairs are completed, back up the database.

在数据库紧急模式下纠正错误Resolving Errors in Database Emergency Mode

如果已通过使用 ALTER DATABASE 语句将数据库设置为紧急模式,那么,假如指定了 REPAIR_ALLOW_DATA_LOSS 选项,则 DBCC CHECKDB 可以对数据库执行某些特殊修复。When a database has been set to emergency mode by using the ALTER DATABASE statement, DBCC CHECKDB can perform some special repairs on the database if the REPAIR_ALLOW_DATA_LOSS option is specified. 这些修复可能允许那些在普通情况下无法恢复的数据库在物理一致状态下重新联机。These repairs may allow for ordinarily unrecoverable databases to be brought back online in a physically consistent state. 这些修复应当是最后手段,并且只有在无法从备份还原数据库时才采用。These repairs should be used as a last resort and only when you cannot restore the database from a backup. 如果将数据库设置为紧急模式,则该数据库将标记为 READ_ONLY 并禁用日志记录,而且只有 sysadmin 固定服务器角色的成员才能访问它。When the database is set to emergency mode, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.

备注

不能在紧急模式下在用户事务内部运行 DBCC CHECKDB 命令并在执行之后回滚事务。You cannot run the DBCC CHECKDB command in emergency mode inside a user transaction and roll back the transaction after execution.

数据库处于紧急模式并且以 REPAIR_ALLOW_DATA_LOSS 子句运行 DBCC CHECKDB 时,将执行以下操作:When the database is in emergency mode and DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause is run, the following actions are taken:

  • DBCC CHECKDB 将使用由于 I/O 或校验和错误而标记为不可访问的页,就如同这些错误没有出现过一样。DBCC CHECKDB uses pages that have been marked inaccessible because of I/O or checksum errors, as if the errors have not occurred. 这样操作将增加从数据库恢复数据的机会。Doing this increases the chances for data recovery from the database.
  • DBCC CHECKDB 将尝试使用常规的基于日志的恢复方法恢复数据库。DBCC CHECKDB attempts to recover the database using regular log-based recovery techniques.
  • 如果由于事务日志损坏而导致数据库恢复失败,则将重新生成事务日志。If, because of transaction log corruption, database recovery is unsuccessful, the transaction log is rebuilt. 重新生成事务日志可能导致事务一致性丢失。Rebuilding the transaction log may result in the loss of transactional consistency.

警告

REPAIR_ALLOW_DATA_LOSS 选项是 SQL ServerSQL Server 支持的功能。The REPAIR_ALLOW_DATA_LOSS option is a supported feature of SQL ServerSQL Server. 但是,其可能并非总是使数据库处于物理上一致的状态的最佳选项。However, it may not always be the best option for bringing a database to a physically consistent state. 如果成功,REPAIR_ALLOW_DATA_LOSS 选项可能会导致一些数据丢失。If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. 实际上,它可能导致的数据丢失多于用户从上次已知成功备份还原数据库导致的数据丢失。In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. MicrosoftMicrosoft 始终建议用户将从上次已知成功备份还原作为从由 DBCC CHECKDB 报告的错误恢复的主要方法。always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. REPAIR_ALLOW_DATA_LOSS 选项不是从已知成功备份还原的替代方法 。The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup. 这是一个紧急选项,仅当不可从备份恢复时建议作为“最后手段”使用。It is an emergency "last resort" option recommended for use only if restoring from a backup is not possible.

重新生成日志后,没有完全的 ACID 保证。After rebuilding the log, there is no full ACID guarantee.

重新生成日志后,将自动执行 DBCC CHECKDB,其将报告并修正物理一致性问题。After rebuilding the log, DBCC CHECKDB will be automatically performed and will both report and correct physical consistency issues.

必须手动验证逻辑数据一致性和业务逻辑实施的约束。Logical data consistency and business logic enforced constraints must be validated manually.

事务日志大小将保留为其默认大小,必须手动将其调整回其最近大小。The transaction log size will be left to its default size and must be manually adjusted back to its recent size.

如果 DBCC CHECKDB 命令成功,则数据库将在物理上是一致的,并且数据库状态将设置为 ONLINE。If the DBCC CHECKDB command succeeds, the database is in a physically consistent state and the database status is set to ONLINE. 但是,数据库可能包含一种或多种事务不一致的情况。However, the database may contain one or more transactional inconsistencies. 我们建议运行 DBCC CHECKCONSTRAINTS 来标识任何业务逻辑缺陷,并立即备份数据库。We recommend that you run DBCC CHECKCONSTRAINTS to identify any business logic flaws and immediately back up the database. 如果 DBCC CHECKDB 命令失败,则无法修复数据库。If the DBCC CHECKDB command fails, the database cannot be repaired.

在复制的数据库中运行具有 REPAIR_ALLOW_DATA_LOSS 的 DBCC CHECKDBRunning DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS in Replicated Databases

运行具有 REPAIR_ALLOW_DATA_LOSS 选项的 DBCC CHECKDB 命令可能会影响用户数据库(发布数据库和订阅数据库)以及由复制使用的分发数据库。Running the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can affect user databases (publication and subscription databases) and the distribution database used by replication. 发布数据库和订阅数据库包括已发布的表和复制元数据表。Publication and subscription databases include published tables and replication metadata tables. 请注意这些数据库中的下列潜在问题:Be aware of the following potential issues in these databases:

  • 已发布的表。Published tables. 可能不会复制由 CHECKDB 进程为修复损坏的用户数据而执行的操作:Actions performed by the CHECKDB process to repair corrupt user data might not be replicated:
  • 合并复制使用触发器跟踪对已发布的表所做的更改。Merge replication uses triggers to track changes to published tables. 如果 CHECKDB 进程插入、更新或删除了行,则触发器不会激发;因此,更改将不会复制。If rows are inserted, updated, or deleted by the CHECKDB process, triggers do not fire; therefore, the change is not replicated.
  • 事务复制使用事务日志跟踪对已发布的表所做的更改。Transactional replication uses the transaction log to track changes to published tables. 然后,日志读取器代理将这些更改移动到分发数据库。The Log Reader Agent then moves these changes to the distribution database. 某些 DBCC 修复即使记入日志,仍然无法由日志读取器代理复制。Some DBCC repairs, although logged, cannot be replicated by the Log Reader Agent. 例如,如果数据页由 CHECKDB 进程释放,则日志读取器代理不会将它翻译为 DELETE 语句;因此,更改将不会复制。For example, if a data page is deallocated by the CHECKDB process, the Log Reader Agent does not translate this to a DELETE statement; therefore, the change is not replicated.
  • 复制元数据表。Replication metadata tables. 由 CHECKDB 进程为修复损坏的复制元数据表而执行的操作需要删除并重新配置复制。Actions performed by the CHECKDB process to repair corrupt replication metadata tables require removing and reconfiguring replication.

如果必须对用户数据库或分发数据库运行具有 REPAIR_ALLOW_DATA_LOSS 选项的 DBCC CHECKDB 命令:If you have to run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option on a user database or distribution database:

  1. 静止系统:停止此数据库和复制拓扑中其他所有数据库上的活动,然后尝试同步所有节点。Quiesce the system: Stop activity on the database and at all other databases in the replication topology, and then try to synchronize all nodes. 有关详细信息,请参阅停止复制拓扑(复制 Transact-SQL 编程)For more information, see Quiesce a Replication Topology (Replication Transact-SQL Programming).
  2. 执行 DBCC CHECKDB。Execute DBCC CHECKDB.
  3. 如果 DBCC CHECKDB 报表包括分发数据库中任何表或用户数据库中任何复制元数据表的修复,则请删除并重新配置复制。If the DBCC CHECKDB report includes repairs for any tables in the distribution database or any replication metadata tables in a user database, remove and reconfigure replication. 有关详细信息,请参阅禁用发布和分发For more information, see Disable Publishing and Distribution.
  4. 如果 DBCC CHECKDB 报表包括任何已复制表的修复,则请执行数据验证以确定发布数据库和订阅数据库中的数据之间是否存在差异。If the DBCC CHECKDB report includes repairs for any replicated tables, perform data validation to determine whether there are differences between the data in the publication and subscription databases.

结果集Result Sets

DBCC CHECKDB 返回以下结果集。DBCC CHECKDB returns the following result set. 这些值可能有所不同,除非指定 ESTIMATEONLY、PHYSICAL_ONLY 或 NO_INFOMSGS 选项:The values might vary except when the ESTIMATEONLY, PHYSICAL_ONLY, or NO_INFOMSGS options are specified:

 DBCC results for 'model'.    
    
 Service Broker Msg 9675, Level 10, State 1: Message Types analyzed: 13.    
    
 Service Broker Msg 9676, Level 10, State 1: Service Contracts analyzed: 5.    
    
 Service Broker Msg 9667, Level 10, State 1: Services analyzed: 3.    
    
 Service Broker Msg 9668, Level 10, State 1: Service Queues analyzed: 3.    
    
 Service Broker Msg 9669, Level 10, State 1: Conversation Endpoints analyzed: 0.    
    
 Service Broker Msg 9674, Level 10, State 1: Conversation Groups analyzed: 0.    
    
 Service Broker Msg 9670, Level 10, State 1: Remote Service Bindings analyzed: 0.    
    
 DBCC results for 'sys.sysrowsetcolumns'.    
    
 There are 630 rows in 7 pages for object 'sys.sysrowsetcolumns'.    
    
 DBCC results for 'sys.sysrowsets'.    
    
 There are 97 rows in 1 pages for object 'sys.sysrowsets'.    
    
 DBCC results for 'sysallocunits'.    
    
 There are 195 rows in 3 pages for object 'sysallocunits'.    
    
 There are 0 rows in 0 pages for object "sys.sysasymkeys".    
    
 DBCC results for 'sys.syssqlguides'.    
    
 There are 0 rows in 0 pages for object "sys.syssqlguides".    
    
 DBCC results for 'sys.queue_messages_1977058079'.    
    
 There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".    
    
 DBCC results for 'sys.queue_messages_2009058193'.    
    
 There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".    
    
 DBCC results for 'sys.queue_messages_2041058307'.    
    
 There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".    
    
 CHECKDB found 0 allocation errors and 0 consistency errors in database 'model'.    
    
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.    

指定 NO_INFOMSGS 时,DBCC CHECKDB 返回以下结果集(消息):DBCC CHECKDB returns the following result set (message) when NO_INFOMSGS is specified:

 The command(s) completed successfully.

指定 PHYSICAL_ONLY 时,DBCC CHECKDB 返回以下结果集:DBCC CHECKDB returns the following result set when PHYSICAL_ONLY is specified:

 DBCC results for 'model'.    
    
 CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.  
    
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

指定 ESTIMATEONLY 时,DBCC CHECKDB 返回以下结果集。DBCC CHECKDB returns the following result set when ESTIMATEONLY is specified.

 Estimated TEMPDB space needed for CHECKALLOC (KB)    
    
 -------------------------------------------------  
    
 13   
    
 (1 row(s) affected)   
    
 Estimated TEMPDB space needed for CHECKTABLES (KB)    
    
 --------------------------------------------------    
    
 57 
    
 (1 row(s) affected)  
    
 DBCC execution completed. If DBCC printed error messages, contact your system administrator.

权限Permissions

要求具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

示例Examples

A.A. 检查当前数据库和其他数据库Checking both the current and another database

下面的示例将对当前数据库和 DBCC CHECKDB 数据库执行 AdventureWorks2012AdventureWorks2012The following example executes DBCC CHECKDB for the current database and for the AdventureWorks2012AdventureWorks2012 database.

-- Check the current database.    
DBCC CHECKDB;    
GO    
-- Check the AdventureWorks2012 database without nonclustered indexes.    
DBCC CHECKDB (AdventureWorks2012, NOINDEX);    
GO    

B.B. 检查当前数据库,取消信息性消息Checking the current database, suppressing informational messages

以下示例检查当前数据库,并取消所有信息性消息。The following example checks the current database and suppresses all informational messages.

DBCC CHECKDB WITH NO_INFOMSGS;    
GO    

另请参阅See Also

DBCC (Transact-SQL)DBCC (Transact-SQL)
查看数据库快照的稀疏文件大小 (Transact-SQL)View the Size of the Sparse File of a Database Snapshot (Transact-SQL)
sp_helpdb (Transact-SQL)sp_helpdb (Transact-SQL)
系统表 (Transact-SQL)System Tables (Transact-SQL)