DBCC (Transact-SQL)

 

Updated: November 16, 2015

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

Command categoryPerform
MaintenanceMaintenance tasks on a database, index, or filegroup.
MiscellaneousMiscellaneous tasks such as enabling trace flags or removing a DLL from memory.
InformationalTasks that gather and display various types of information.
ValidationValidation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

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. For more information, see Database Snapshots (SQL Server).

DBCC CHECKALLOCDBCC CHECKDB
DBCC CHECKCATALOGDBCC CHECKFILEGROUP
DBCC CHECKTABLE

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.

An internal database snapshot is not created when a DBCC command is executed:

  • Against master, and the instance of SQL Server is running in single-user mode.

  • 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.

  • Against a database that has been set in emergency mode by using the ALTER DATABASE statement.

  • Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.

  • Using the WITH TABLOCK option. In this case, DBCC honors the request by not creating a database snapshot.

The DBCC commands use table locks instead of the internal database snapshots when the command is executed against the following:

  • A read-only filegroup

  • An FAT file system

  • A volume that does not support 'named streams'

  • A volume that does not support 'alternate streams'

System_CAPS_ICON_note.jpg Note


Trying to run DBCC CHECKALLOC, or the equivalent part of DBCC CHECKDB, by using the WITH TABLOCK option requires a database X lock. This database lock cannot be set on tempdb or master and will probably fail on all other databases.

System_CAPS_ICON_note.jpg Note


DBCC CHECKDB fails when it is run against master if an internal database snapshot cannot be created.

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. 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.

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 phaseDescriptionProgress reporting granularity
DBCC TABLE CHECKThe logical and physical consistency of the objects in the database is checked during this phase.Progress reported at the database page level.

The progress reporting value is updated for each 1000 database pages that are checked.
DBCC TABLE REPAIRDatabase 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 CHECKAllocation structures in the database are checked during this phase.

Note: DBCC CHECKALLOC performs the same checks.
Progress is not reported
DBCC ALLOC REPAIRDatabase 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 CHECKDatabase system tables are checked during this phase.Progress reported at the database page level.

The progress reporting value is updated for every 1000 database pages that are checked.
DBCC SYS REPAIRDatabase 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 CHECKSQL Server Service Broker objects are checked during this phase.

Note: This phase is not executed when DBCC CHECKTABLE is executed.
Progress is not reported.
DBCC CHECKCATALOGThe consistency of database catalogs are checked during this phase.

Note: This phase is not executed when DBCC CHECKTABLE is executed.
Progress is not reported.
DBCC IVIEW CHECKThe 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.
DBCC INPUTBUFFERDBCC SHOWCONTIG
DBCC OPENTRANDBCC SQLPERF
DBCC OUTPUTBUFFERDBCC TRACESTATUS
DBCC PROCCACHEDBCC USEROPTIONS
DBCC SHOW_STATISTICS
DBCC CHECKALLOCDBCC CHECKFILEGROUP
DBCC CHECKCATALOGDBCC CHECKIDENT
DBCC CHECKCONSTRAINTSDBCC CHECKTABLE
DBCC CHECKDB
DBCC CLEANTABLEDBCC INDEXDEFRAG
DBCC DBREINDEXDBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERSDBCC SHRINKFILE
DBCC FREEPROCCACHEDBCC UPDATEUSAGE
DBCC dllname (FREE)DBCC HELP
DBCC FLUSHAUTHCACHEDBCC TRACEOFF
DBCC FREESESSIONCACHEDBCC TRACEON
DBCC FREESYSTEMCACHEDBCC CLONEDATABASE 

 Applies to: SQL Server 2014 Service Pack 2.

Community Additions

ADD
Show: