DBCC (Transact-SQL)DBCC (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

Transact-SQLTransact-SQL 编程语言提供 DBCC 语句以作为 SQL ServerSQL Server 的数据库控制台命令。The Transact-SQLTransact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL ServerSQL Server.

数据库控制台命令语句可分为以下类别。Database Console Command statements are grouped into the following categories.

命令类别Command category 执行Perform
维护Maintenance 对数据库、索引或文件组进行维护的任务。Maintenance tasks on a database, index, or filegroup.
杂项Miscellaneous 杂项任务,如启用跟踪标志或从内存中删除 DLL。Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
信息Informational 收集并显示各种类型信息的任务。Tasks that gather and display various types of information.
验证Validation 对数据库、表、索引、目录、文件组或数据库页的分配进行的验证操作。Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC 命令使用输入参数并返回值。DBCC commands take input parameters and return values. 所有 DBCC 命令参数都可以接受 Unicode 和 DBCS 文字。All DBCC command parameters can accept both Unicode and DBCS literals.

DBCC 内部数据库快照用法DBCC Internal Database Snapshot Usage

以下 DBCC 命令对 数据库引擎Database Engine创建的内部只读数据库快照执行操作。The following DBCC commands operate on an internal read-only database snapshot that the 数据库引擎Database Engine creates. 这样可以防止在执行这些命令时出现阻塞和并发问题。This prevents blocking and concurrency problems when these commands are executed. 有关详细信息,请参阅数据库快照 (SQL Server)For more information, see Database Snapshots (SQL Server).

  • DBCC CHECKALLOCDBCC CHECKALLOC
  • DBCC CHECKCATALOGDBCC CHECKCATALOG
  • DBCC CHECKDBDBCC CHECKDB
  • DBCC CHECKFILEGROUPDBCC CHECKFILEGROUP
  • DBCC CHECKTABLEDBCC CHECKTABLE

在执行这些 DBCC 命令之一时,数据库引擎Database Engine创建一个数据库快照,并将其置于在事务上一致的状态。When you execute one of these DBCC commands, the 数据库引擎Database Engine creates a database snapshot and brings it to a transactionally consistent state. 然后,DBCC 命令对该快照运行检查。The DBCC command then runs the checks against this snapshot. DBCC 命令完成后,将删除该快照。After the DBCC command is completed, this snapshot is dropped.

有时,不需要内部数据库快照或无法创建内部数据库快照。Sometimes an internal database snapshot is not required or cannot be created. 出现这种情况时,将针对实际数据库执行 DBCC 命令。When this occurs, the DBCC command executes against the actual database. 如果数据库联机,DBCC 命令使用表锁确保它所检查的对象的一致性。If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. 该行为与指定 WITH TABLOCK 选项时的行为相同。This behavior is the same as if the WITH TABLOCK option were specified.

执行 DBCC 命令时,不创建内部数据库快照:An internal database snapshot is not created when a DBCC command is executed:

  • 针对 master,并且 SQL ServerSQL Server 的实例以单用户模式运行 。Against master, and the instance of SQL ServerSQL Server is running in single-user mode.
  • 针对 master 之外的其他数据库,但已使用 ALTER DATABASE 语句将该数据库置于单用户模式 。Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
  • 针对只读数据库。Against a read-only database.
  • 针对已使用 ALTER DATABASE 语句设置为紧急模式的数据库。Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
  • 针对 tempdb 。Against tempdb. 在这种情况下,由于内部限制不能创建数据库快照。In this case, a database snapshot cannot be created because of internal restrictions.
  • 使用 WITH TABLOCK 选项。Using the WITH TABLOCK option. 在这种情况下,DBCC 允许该请求但不创建数据库快照。In this case, DBCC honors the request by not creating a database snapshot.

当针对以下对象执行 DBCC 命令时,该命令将使用表锁而不是内部数据库快照:The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:

  • 只读文件组A read-only filegroup
  • FAT 文件系统An FAT file system
  • 不支持“命名流”的卷A volume that does not support 'named streams'
  • 不支持“备用流”的卷A volume that does not support 'alternate streams'

备注

尝试使用 WITH TABLOCK 选项运行 DBCC CHECKALLOC 或 DBCC CHECKDB 的等价部分时,需要使用数据库 X 锁。Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. 该数据库锁不能对 tempdb 或 master 设置,对其他所有数据库进行设置时也可能会失败 。This database lock cannot be set on tempdb or master and will probably fail on all other databases.

备注

如果无法创建内部数据库快照,则对 master 运行 DBCC CHECKDB 时将失败 。DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.

DBCC 命令的进度报告Progress Reporting for DBCC Commands

sys.dm_exec_requests 目录视图包含有关 DBCC CHECKDB、CHECKFILEGROUP 和 CHECKTABLE 命令的进度和当前执行阶段的信息 。The sys.dm_exec_requests catalog view contains information about the progress and the current phase of execution of the DBCC CHECKDB, CHECKFILEGROUP, and CHECKTABLE commands. percent_complete 列指示命令完成的百分比,command 列报告命令执行的当前阶段 。The percent_complete column indicates the percentage complete of the command, and the command column reports the current phase of the execution of the command.

进度单位的定义取决于 DBCC 命令的当前执行阶段。The definition of a unit of progress depends on the current phase of execution of the DBCC command. 有时,根据数据库页的粒度报告进度,而在其他阶段,则根据单个数据库或分配修复的粒度报告进度。Sometimes progress is reported at the granularity of a database page, in other phases it is reported at the granularity of a single database or allocation repair. 下表对每个执行阶段以及命令报告进度的粒度进行了说明。The following table describes each phase of execution, and the granularity at which the command reports progress.

执行阶段Execution phase 描述Description 进度报告粒度Progress reporting granularity
DBCC TABLE CHECKDBCC TABLE CHECK 在该阶段中将检查数据库中对象的逻辑和物理一致性。The logical and physical consistency of the objects in the database is checked during this phase. 在数据库页级别报告进度。Progress reported at the database page level.

每检查 1000 个数据库页更新进程报告值一次。The progress reporting value is updated for each 1000 database pages that are checked.
DBCC TABLE REPAIRDBCC TABLE REPAIR 如果指定 REPAIR_FAST、REPAIR_REBUILD 或 REPAIR_ALLOW_DATA_LOSS 并且存在必须修复的对象错误,则在该阶段执行数据库修复。Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are object errors that must be repaired. 在单个修复级别报告进度。Progress reported at the individual repair level.

每次完成修复时更新计数器。The counter is updated for each repair that is completed.
DBCC ALLOC CHECKDBCC ALLOC CHECK 在该阶段中将检查数据库中的分配结构。Allocation structures in the database are checked during this phase.

注意:DBCC CHECKALLOC 执行相同检查。Note: DBCC CHECKALLOC performs the same checks.
不报告进度Progress is not reported
DBCC ALLOC REPAIRDBCC ALLOC REPAIR 如果指定 REPAIR_FAST、REPAIR_REBUILD 或 REPAIR_ALLOW_DATA_LOSS 并且存在必须修复的分配错误,则在该阶段执行数据库修复。Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are allocation errors that must be repaired. 不报告进度。Progress is not reported.
DBCC SYS CHECKDBCC SYS CHECK 在该阶段中将检查数据库系统表。Database system tables are checked during this phase. 在数据库页级别报告进度。Progress reported at the database page level.

每检查 1000 个数据库页更新进程报告值一次。The progress reporting value is updated for every 1000 database pages that are checked.
DBCC SYS REPAIRDBCC SYS REPAIR 如果指定 REPAIR_FAST、REPAIR_REBUILD 或 REPAIR_ALLOW_DATA_LOSS 并且存在必须修复的系统表错误,则在该阶段执行数据库修复。Database repairs are performed during this phase if REPAIR_FAST, REPAIR_REBUILD, or REPAIR_ALLOW_DATA_LOSS is specified, and there are system table errors that must be repaired. 在单个修复级别报告进度。Progress reported at the individual repair level.

每次完成修复时更新计数器。The counter is updated for each repair that is completed.
DBCC SSB CHECKDBCC SSB CHECK 在该阶段中将检查 SQL ServerSQL Server Service Broker 对象。SQL ServerSQL Server Service Broker objects are checked during this phase.

注意:执行 DBCC CHECKTABLE 时,不执行此阶段。Note: This phase is not executed when DBCC CHECKTABLE is executed.
不报告进度。Progress is not reported.
DBCC CHECKCATALOGDBCC CHECKCATALOG 在该阶段中将检查数据库目录的一致性。The consistency of database catalogs are checked during this phase.

注意:执行 DBCC CHECKTABLE 时,不执行此阶段。Note: This phase is not executed when DBCC CHECKTABLE is executed.
不报告进度。Progress is not reported.
DBCC IVIEW CHECKDBCC IVIEW CHECK 在该阶段中将检查数据库中存在的任何索引视图的逻辑一致性。The logical consistency of any indexed views present in the database is checked during this phase. 在被检查的单个数据库视图级别报告进度。Progress reported at the level of the individual database view that is being checked.

信息语句Informational Statements

DBCC INPUTBUFFERDBCC INPUTBUFFER DBCC SHOWCONTIGDBCC SHOWCONTIG
DBCC OPENTRANDBCC OPENTRAN DBCC SQLPERFDBCC SQLPERF
DBCC OUTPUTBUFFERDBCC OUTPUTBUFFER DBCC TRACESTATUSDBCC TRACESTATUS
DBCC PROCCACHEDBCC PROCCACHE DBCC USEROPTIONSDBCC USEROPTIONS
DBCC SHOW_STATISTICSDBCC SHOW_STATISTICS

验证语句Validation Statements

DBCC CHECKALLOCDBCC CHECKALLOC DBCC CHECKFILEGROUPDBCC CHECKFILEGROUP
DBCC CHECKCATALOGDBCC CHECKCATALOG DBCC CHECKIDENTDBCC CHECKIDENT
DBCC CHECKCONSTRAINTSDBCC CHECKCONSTRAINTS DBCC CHECKTABLEDBCC CHECKTABLE
DBCC CHECKDBDBCC CHECKDB

维护语句Maintenance Statements

DBCC CLEANTABLEDBCC CLEANTABLE DBCC INDEXDEFRAGDBCC INDEXDEFRAG
DBCC DBREINDEXDBCC DBREINDEX DBCC SHRINKDATABASEDBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERSDBCC DROPCLEANBUFFERS DBCC SHRINKFILEDBCC SHRINKFILE
DBCC FREEPROCCACHEDBCC FREEPROCCACHE DBCC UPDATEUSAGEDBCC UPDATEUSAGE

杂项语句Miscellaneous Statements

DBCC dllname (FREE)DBCC dllname (FREE) DBCC HELPDBCC HELP
DBCC FLUSHAUTHCACHEDBCC FLUSHAUTHCACHE DBCC TRACEOFFDBCC TRACEOFF
DBCC FREESESSIONCACHEDBCC FREESESSIONCACHE DBCC TRACEONDBCC TRACEON
DBCC FREESYSTEMCACHEDBCC FREESYSTEMCACHE DBCC CLONEDATABASEDBCC CLONEDATABASE

适用对象SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和更高版本。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and later.