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
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
DBCC CHECKFILEGROUP
DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS
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
DBCC OUTPUTBUFFER
This DBCC command is used to return the current value of the output buffer.
DBCC SHOW_STATISTICS
DBCC TRACESTATUS
DBCC PROCCACHE
DBCC SHOWCONTIG
DBCC USEROPTIONS
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)
DBCC HELP
DBCC TRACEOFF
DBCC TRACEON
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE
