DBCC Commands in SQL Server

DBCC stands for Database Console Command, these commands are used for database maintenance, validation, informational and some other miscellaneous tasks. DBCC commands take input parameters and return values. DBCC commands accept both Unicode and DBCS literals. It can also be run from Command line or query analyzer.

DBCC statements are grouped into the following categories.

DBCC Category	Perform
Maintenance	Used for Database, Index and Filegroup Maintenance.
Validation	Used for Database, Table, Index, Catalog, Filegroup and allocation of data pages Validations.
Informational	Used to gather various types of information from database.
Miscellaneous	Such as enabling trace flags or removing DDL from memory.

DBCC Maintenance Category Commands

Below is the list of all DBCC commands which are used for maintenance purpose.

DBCC CLEANTABLE	DBCC DBREINDEX	DBCC DROPCLEANBUFFERS
DBCC INDEXDEFRAG	DBCC SHRINKDATABASE	DBCC SHRINKFILE
DBCC FREEPROCCACHE	DBCC UPDATEUSAGE

DBCC CLEANTABLE

This DBCC command is used to remove spaces occupied by columns when they are removed.

DBCC DBREINDEX

This DBCC command is used to recreate the indexes for a particular table. This DBCC command rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation.

DBCC DROPCLEANBUFFERS

This DBCC command is used to drop clean buffers from the buffer pool.

DBCC INDEXDEFRAG

This DBCC command is used to defragment the clustered and secondary indexes associated with the particular table. The index defragmentation is carried out using the fill factor specified at the time of creation of indexes. While its operation is strikingly similar to that of DBCC DBREINDEX, unlike DBCC INDEXFRAG it does not allow new fill factor to be specified.

DBCC SHRINKDATABASE

This DBCC command is used to reduce the size of a database. This DBCC command reduces the physical size of the database log file. An alternate way to shrink a database is to use the commander ALTER DATABASE.

DBCC SHRINKFILE

This DBCC command is used to reduce the size of a data file or log file of a particular database. The file could also be shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.

DBCC FREEPROCCACHE

This DBCC command is used to remove all elements from the procedure cache.

DBCC UPDATEUSAGE

This DBCC command is used to correct inaccuracies in the page and row statistics in the views.

DBCC Validation Category Commands

Below is the list of all DBCC commands which are used for validation purpose.

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.

DBCC CHECKALLOC	DBCC CHECKDB	DBCC CHECKTABLE
DBCC CHECKCATALOG	DBCC CHECKFILEGROUP	DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS

DBCC CHECKALLOC

This DBCC command is used to checks whether every extent allocated by the system has been allocated and whether there are extents that have not been allocated.

DBCC CHECKDB

This DBCC command is used to check integrity and allocation of specific objects in a database. 

DBCC CHECKTABLE

This DBCC command is used to check the integrity of a table and all the pages and structures which comprise the table. Both physical and logical checks are performed in this case. However, a PHYSICAL ONLY option can be used to check for physical consistency alone.

DBCC CHECKCATALOG

This DBCC command is used to check for consistency between system tables in the system catalog

DBCC CHECKFILEGROUP

This DBCC command is used to check allocation and structural integrity of tables.

DBCC CHECKIDENT

This DBCC command is used to check identity value of specified table.

DBCC CHECKCONSTRAINTS

This DBCC command is used to check integrity of specific constraints.

DBCC Informational Category Commands

Below is the list of all DBCC commands which are used for informational purpose.

DBCC INPUTBUFFER	DBCC OPENTRAN	DBCC SQLPERF
DBCC OUTPUTBUFFER	DBCC SHOW_STATISTICS	DBCC TRACESTATUS
DBCC PROCCACHE	DBCC SHOWCONTIG	DBCC USEROPTIONS

DBCC INPUTBUFFER

This DBCC command is used to display the last statement stored in the buffer.

DBCC OPENTRAN

This DBCC command is used to display information about the oldest open transaction.

DBCC SQLPERF

This DBCC command is used to show transaction log statistics

DBCC OUTPUTBUFFER

This DBCC command is used to return the current value of the output buffer.

DBCC SHOW_STATISTICS

This DBCC command is used to show current distribution statistics

DBCC TRACESTATUS

This DBCC command is used to display status of trace flags.

DBCC PROCCACHE

This DBCC command is used to display information about procedure cache.

DBCC SHOWCONTIG

This DBCC command is used to display fragmentation information

DBCC USEROPTIONS

This DBCC command is used to return set as ACTIVE

DBCC Miscellaneous Category Commands

Below is the list of all DBCC miscellaneous commands.

DBCC DLLNAME(FREE)	DBCC FREESYSTEMCACHE	DBCC TRACEON
DBCC FREESESSIONCACHE	DBCC HELP	DBCC TRACEOFF

DBCC dllname (FREE)

This DBCC command is used to unload a particular stored procedure DLL from memory.

DBCC HELP

This DBCC command is used to return syntax information.

DBCC TRACEOFF

This DBCC command is used to disable a trace flag.

DBCC TRACEON

This DBCC command is used to turn on a specific trace flag.

DBCC FREESESSIONCACHE

This DBCC command is used to free the session cache.

DBCC FREESYSTEMCACHE

This DBCC command is used to free system cache.

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning !