Tuesday, July 30, 2013

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.