What is DBCC commands in SQL server?

SQL server provides following database management commands.

CHECKPOINT

KILL STATS JOB

DBCC

RECONFIGURE

KILL

SHUTDOWN

KILL QUERY NOTIFICATION SUBSCRIPTION

Out of above management commands, today we are going to learn about DBCC Commands.

DBCC stands for Database Console Command.We can also calls them Database Consistency Checker. Consistency Checker means, These commands are use to check consistency of database. Used to help making database more and more consistent.

These are very powerful SQL Server commands, These commands belongs to SQL Server management commands. With the help of DBCC commands we can understand Sql Server with much more details i.e What is going on inside Sql Server ?

How we can make it more and more consistent ?

Mocrosoft sql server provides a lot of DBCC commands. We can get list of commands by executing following query.

DBCC HELP ('?')

OUTPUT:

checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checktable
cleantable
dbreindex
dropcleanbuffers
flushauthcache
free
freeproccache
freesessioncache
freesystemcache
help
indexdefrag
inputbuffer
opentran
outputbuffer
pintable
proccache
show_statistics
showcontig
shrinkdatabase
shrinkfile
sqlperf
traceoff
traceon
tracestatus
unpintable
updateusage
useroptions

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Out of them most of them are well documented and few are undocumented. You can get documented DBCC Commands by following Query.

DBCC HELP ('help')

OUTPUT:
dbcc help 
(
    { 'dbcc_statement' | @dbcc_statement_var | '?' }
)
    [ WITH NO_INFOMSGS ]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC HELP ('checkdb')

OUTPUT:
 
dbcc checkdb 
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
            [ , [ MAXDOP =  ] ]
        }
    ]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Microsoft grouped these Database Console Command into following four categories.

1) Maintenance

2) Miscellaneous

3) Informational

4) Validation

1) Maintenance Statements: Maintenance Statements, are used for maintenance tasks on a database, index, or filegroup.

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC SHRINKFILE

DBCC FREEPROCCACHE

DBCC UPDATEUSAGE

2) Miscellaneous Statements: Miscellaneous Statements, are used for miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

DBCC dllname (FREE)

DBCC HELP

DBCC FLUSHAUTHCACHE

DBCC TRACEOFF

DBCC FREESESSIONCACHE

DBCC TRACEON

DBCC FREESYSTEMCACHE

DBCC CLONEDATABASE

3) Informational Statements: Informational Statements, are used for tasks that gather and display various types of information.

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

4) Validation Statements: Validation Statements, are used for validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS

DBCC CHECKTABLE

DBCC CHECKDB

The following DBCC commands operate on an internal read-only database snapshot created by database engine.

This prevents blocking and concurrency problems when these commands are executed.

DBCC CHECKALLOC

DBCC CHECKCATALOG

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKTABLE