Microsoft SQL Server DBCC Commands list

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

If you work with Microsoft SQL Server, you have probably already come across the DBCC (DataBase Consistency Check) commands, which are a useful set of system commands for very various purposes. Some are documented  here at Microsoft developer network  and some are not. Having worked with Microsoft SQL Server for quite some time now, I thought it was about time to compile a list of all DBCC commands that I can find.
The Microsoft SQL Server DBCC Commands list below is not complete yet, neither in commands or in info about commands. I will add more commands and info as I can get my hands on it, please let me know if you have anything to add to the list.

Here you can also view the Trace Flag list

Microsoft SQL Server DBCC Commands list

NB! Please be aware that many of these DBCC Commands in the list below are undocumented and therefore potentially dangerous to your system. Do not try these on your production system unless you have tried it extensively on your test systems first.

Command Name: 
Syntax: dbcc addinstance (objectname, instancename)
Info:
Link: N/A

Command Name: dbcc auditevent
Syntax: dbcc auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid, objname, servername, providername)
Info:
Link: N/A

Command Name: dbcc autopilot
Syntax: dbcc autopilot (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
Info: Work with hypotetical indexes
Link: https://www.mssqltips.com/sqlservertip/3246/sql-server-performance-tuning-with-hypothetical-indexes/

Command Name: dbcc buffer
Syntax: dbcc buffer ( {‘dbname’ | dbid} [, {‘objname’ | objid} [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])
Info:
Link: N/A

Command Name: dbcc callfulltext
Syntax: ???
Info: system sp use only
Link: N/A

Command Name: dbcc checkalloc
Syntax: dbcc checkalloc
(
[ { ‘database_name’ | database_id | 0 } ]
[ , NOINDEX |
{ REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)

[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
}
]
Info:
Link: N/A

 

Command Name: dbcc checkcatalog
Syntax: dbcc checkcatalog
[
( { ‘database_name’ | database_id | 0 } )
]

[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc checkconstraints
Syntax: dbcc checkconstraints
[
( { ‘table_name’ | table_id | ‘constraint_name’ | constraint_id } )
]

[ WITH
{ ALL_CONSTRAINTS | ALL_ERRORMSGS }
[ , [ NO_INFOMSGS ] ]
]
Info:
Link: N/A

Command Name: dbcc checkdb
Syntax: 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 ] ]
}
]
Info:
Link: N/A

Command Name: dbcc checkfilegroup
Syntax: dbcc checkfilegroup
(
[ { ‘filegroup_name’ | filegroup_id | 0 } ]
[ , NOINDEX ]
)

[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
}
]
Info:
Link: N/A

Command Name: dbcc checkident
Syntax: dbcc checkident
(
‘table_name’
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
} ]
)

[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc checkprimaryfile
Syntax: dbcc checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])
Info:
Link: N/A

Command Name: dbcc checktable
Syntax: dbcc checktable
(
{ ‘table_name’ | ‘view_name’ }
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)

[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ EXTENDED_LOGICAL_CHECKS ] ]
}
]
Info:
Link: N/A

Command Name: dbcc cleanpage
Syntax: dbcc cleanpage ({‘dbname’ | dbid}, fileid, pageid)
Info:
Link: N/A

Command Name: dbcc cleantable
Syntax: dbcc cleantable
(
{ ‘database_name’ | database_id | 0 }
, { ‘table_name’ | table_id | ‘view_name’ | view_id }
[ , batch_size ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc clearspacecaches
Syntax: dbcc clearspacecaches (‘dbname’|dbid, ‘table_name’|table_id, ‘index_name’|index_id [, partition_number])
Info:
Link: N/A

Command Name: dbcc collectstats
Syntax: dbcc collectstats (on | off)
Info:
Link: N/A

 

Command Name: dbcc crashdb
Syntax: dbcc crashdb
Info: No help available for Command Name: dbcc statement ‘crashdb’.
Link: N/A

 

Command Name: dbcc csindex
Syntax: dbcc csindex ( {‘dbname’ | dbid}, rowsetid, columnid, rowgroupid, object type, print option, start, end)
Info:
Link: N/A

Command Name: dbcc cursorstats
Syntax: dbcc cursorstats ([spid [,’clear’]])
Info:
Link: N/A

Command Name: dbcc dbreindex
Syntax: dbcc dbreindex
(
‘table_name’
[ , ‘index_name’ [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc dbreindexall 
Syntax: dbcc dbreindexall (dbname|dbid[, type_bitmap])
Info:
Link: N/A

Command Name: dbcc dbrepair
Syntax: dbcc dbrepair (‘dbname’, markdirty | {dropdevice, int} | {repairindex, int, int})
Info:
Link: N/A

Command Name: dbcc debugbreak
Syntax: dbcc debugbreak
Info:
Link: N/A

Command Name: dbcc deleteinstance
Syntax: dbcc deleteinstance (objectname, instancename)
Info:
Link: N/A

Command Name: dbcc detachdb
Syntax: dbcc detachdb ( ‘dbname’ [, fKeep_Fulltext_Index_File (0 | 1)] )
Info:
Link: N/A

Command Name: dbcc dropcleanbuffers
Syntax: dbcc dropcleanbuffers
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc config
Syntax: dbcc config
Info: Shows config infor for the SQL Server Instance, similar to sp_configure
Link: N/A

Command Name: dbcc dbinfo
Syntax: dbcc dbinfo [(‘dbname’)]
Info: Shows info about a database, for example last good DBCC CHECKDB
Link: N/A

Command Name: dbcc dbtable
Syntax: dbcc dbtable [({‘dbname’ | dbid})]
Info:
Link: N/A

Command Name: dbcc lock
Syntax: dbcc lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}]|[{‘STALLREPORTTHESHOLD’, stallthreshold}])
Info:
Link: N/A

Command Name: dbcc log
Syntax: dbcc log (dbname | dbid [,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘dir’, 0|1]|[‘numrecs’,num]|[‘xdesid’,’x:y’]|[‘extent’,’x:y’]
|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,{‘lop’|op}…]
|[‘output’,x,[‘filename’,’x’]]|[‘column’,”]|[‘key’,”]|[‘nolookup’]|[‘allocid’,BIGINT]…]]])
Info:
Link: N/A

Command Name: dbcc page
Syntax: dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Info:
Link: N/A

Command Name: dbcc resource
Syntax: dbcc resource
Info:
Link: N/A

Command Name: dbcc dumptrigger
Syntax: dbcc dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number} | {‘CLEAR’, exception_number})
Info:
Link: N/A

Command Name: dbcc errorlog
Syntax: dbcc errorlog
Info:
Link: N/A

Command Name: dbcc extentinfo
Syntax: dbcc extentinfo [({‘dbname’| dbid | 0} [, {‘tablename’ | tableid} [, {‘indexname’ | indexid | -1} [, partition_number]]])]
Info:
Link: N/A

Command Name: dbcc fileheader
Syntax: dbcc fileheader [( {‘dbname’ | dbid} [, fileid])
Info:
Link: N/A

Command Name: dbcc fixallocation
Syntax: dbcc fixallocation [({‘ADD’ | ‘REMOVE’}, {‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}, filenum, pagenum [, objectid, indexid, partitionid, allocUnitId])
Info:
Link: N/A

Command Name: dbcc flush
Syntax: dbcc flush (‘data’ | ‘log’, dbname | dbid)
Info:
Link: N/A

Command Name: dbcc flushprocindb
Syntax: dbcc flushprocindb (dbid)
Info:
Link: N/A

Command Name: dbcc forceghostcleanup
Syntax: dbcc forceghostcleanup [({‘dbname’ | dbid})]
Info:
Link: N/A

Command Name: dbcc dll_name
Syntax: dbcc dll_name( FREE ) [ WITH NO_INFOMSGS ]
e.g. Command Name: dbcc xp_sample( FREE )
Info:
Link: N/A

Command Name: dbcc freeproccache
Syntax: dbcc freeproccache
[ ( @HANDLE | ‘POOL NAME’ ) ]
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc freesessioncache
Syntax: dbcc freesessioncache
Info:
Link: N/A

Command Name: dbcc freesystemcache
Syntax: dbcc freesystemcache
(
‘ALL’ [, ‘POOL NAME’]
)
[ WITH
{
[ MARK_IN_USE_FOR_REMOVAL ]
[, [ NO_INFOMSGS ] ]
}
]
Info:
Link: N/A

Command Name: dbcc freeze_io
Syntax: dbcc freeze_io (dbname | dbid)
Info:
Link: N/A

Command Name: dbcc help
Syntax: dbcc help
(
{ ‘dbcc_statement’ | @dbcc_statement_var | ‘?’ }
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc incrementinstance
Syntax: dbcc incrementinstance (objectname, countername, instancename, value)
Info:
Link: N/A

Command Name: dbcc ind
Syntax: dbcc ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
Info:
Link: N/A

Command Name: dbcc indexdefrag
Syntax: dbcc indexdefrag
(
{ ‘database_name’ | database_id | 0 }
, { ‘table_name’ | table_id | ‘view_name’ | view_id }
[ , { ‘index_name’ | index_id }
[ , { partition_number | 0 } ] ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc inputbuffer
Syntax: dbcc inputbuffer
(
session_id [ , request_id ]
)
[WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc invalidate_textptr
Syntax: dbcc invalidate_textptr (textptr)
Info:
Link: N/A

Command Name: dbcc invalidate_textptr_objid
Syntax: dbcc invalidate_textptr_objid (objid)
Info:
Link: N/A

Command Name: dbcc loginfo
Syntax: dbcc loginfo [({‘dbname’ | dbid})]
Info:
Link: N/A

Command Name: dbcc mapallocunit
Syntax: dbcc mapallocunit (I8AllocUnitId | {I4part, I2part})
Info:
Link: N/A

Command Name: dbcc memorystatus
Syntax: dbcc memorystatus
Info:
Link: N/A

Command Name: dbcc no_textptr
Syntax: dbcc no_textptr (table_id , max_inline)
Info:
Link: N/A

Command Name: dbcc opentran
Syntax: dbcc opentran
[
( [ { ‘database_name’ | database_id | 0 } ] )
]
[ WITH
{
[ TABLERESULTS ]
[ , [ NO_INFOMSGS ]
}
]
Info:
Link: N/A

Command Name: dbcc optimizer_whatif
Syntax: dbcc optimizer_whatif ({property/cost_number | property_name} [, {integer_value | string_value} ])
Info:
Link: http://www.sqlservice.se/trace-flag-undocumented-commands/

Command Name: dbcc outputbuffer
Syntax: dbcc outputbuffer
(
session_id [ , request_id ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc perfmon
Syntax: dbcc perfmon
Info:
Link: N/A

Command Name: dbcc persiststackhash
Syntax: dbcc persiststackhash (input_hashfile, BUFLATCH_TIMEOUT | ATTENTION | OUTOFLOCKS | LATCH_ATTN | OUTOFLOG | OUTOFMEM | SOS | NO_BUFFS [, SKIPLAST | INCLUDELAST] [,output_hashfile])
Info:
Link: N/A

Command Name: dbcc pintable
Syntax: dbcc pintable (database_id, table_id)
Info:
Link: N/A

Command Name: dbcc proccache
Syntax: dbcc proccache
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc readpage
Syntax: dbcc readpage ({‘dbname’|dbid}, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
Info:
Link: N/A

Command Name: dbcc ruleoff
Syntax: dbcc ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
Info:
Link: N/A

Command Name: dbcc ruleon
Syntax: dbcc ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
Info:
Link: N/A

Command Name: dbcc semetadata
Syntax: dbcc semetadata (object id | name, index id | name [, partition id])
Info:
Link: N/A

Command Name: dbcc setcpuweight
Syntax: dbcc setcpuweight (weight)
Info:
Link: N/A

Command Name: dbcc setinstance
Syntax: dbcc setinstance (objectname, countername, instancename, value)
Info:
Link: N/A

Command Name: dbcc setioweight
Syntax: dbcc setioweight (weight)
Info:
Link: N/A

Command Name: dbcc show_statistics
Syntax: dbcc show_statistics
(
{ ‘table_name’ | ‘view_name’ }
, target
)
[ WITH
{
[ NO_INFOMSGS ]
< option > [ , n ]
}
]
< option > ::=
STAT_HEADER | DENSITY_VECTOR | HISTOGRAM
Info:
Link: N/A

Command Name: dbcc showcontig
Syntax: dbcc showcontig
[ (
{ ‘table_name’ | table_id | ‘view_name’ | view_id }
[ , { ‘index_name’ | index_id } ]
) ]
[ WITH
{
[ ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ] ]
[ , { FAST | ALL_LEVELS } ]
[ , NO_INFOMSGS ]
}
]
Info:
Link: N/A

Command Name: dbcc showfilestats
Syntax: dbcc showfilestats [(file_num)]
Info:
Link: N/A

Command Name: dbcc showoffrules
Syntax: dbcc showoffrules
Info:
Link: N/A

Command Name: dbcc showonrules
Syntax: dbcc showonrules
Info:
Link: N/A

Command Name: dbcc showtext
Syntax: dbcc showtext (‘dbname’ | dbid, {textpointer | {fileid, pageid, slotid [,option]}})
Info:
Link: N/A

Command Name: dbcc showweights
Syntax: dbcc showweights
Info:
Link: N/A

Command Name: dbcc shrinkdatabase
Syntax: dbcc shrinkdatabase
(
{ ‘database_name’ | database_id | 0 }
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc shrinkfile
Syntax: dbcc shrinkfile
(
{ ‘file_name’ | file_id }
{
[ , EMPTYFILE]
| [ [, target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc sqlmgrstats
Syntax: dbcc sqlmgrstats
Info:
Link: N/A

Command Name: dbcc sqlperf
Syntax: dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]
({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})
Info:
Link: N/A

Command Name: dbcc stackdump
Syntax: dbcc stackdump [( [‘dump description’,] {{uid[, spid [, batchid [, ecid]]} | {threadId, ‘THREADID’}]} )]
Info:
Link: N/A

Command Name: dbcc tec
Syntax: dbcc tec [( uid[, spid[, batchid[, ecid]] )]
Info:
Link: N/A

Command Name: dbcc thaw_io (dbname | dbid)
Syntax: dbcc thaw_io (dbname | dbid)
Info:
Link: N/A

Command Name: dbcc traceoff
Syntax: dbcc traceoff
(
trace# [ , …n ] [, -1]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc traceon
Syntax: dbcc traceon
(
trace# [ , …n ] [, -1]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc tracestatus
Syntax: dbcc tracestatus
(
[ [ trace# [ , …n ] ] [ , -1 ] ]
)
[ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc unpintable
Syntax: dbcc unpintable (dbid, table_id)
Info:
Link: N/A

Command Name: dbcc updateusage
Syntax: dbcc updateusage
(
{ ‘database_name’ | database_id | 0 }
[ , { ‘table_name’ | ‘view_name’ | object_id }
[ , { ‘index_name’ | index_id } ] ]
)
[ WITH
{
[ NO_INFOMSGS ]
[ , [ COUNT_ROWS ] ]
}
]
Info:
Link: N/A

Command Name: dbcc useplan
Syntax: dbcc useplan [(number_of_plan)]
Info:
Link: N/A

Command Name: dbcc useroptions
Syntax: dbcc useroptions [ WITH NO_INFOMSGS ]
Info:
Link: N/A

Command Name: dbcc writepage
Syntax: dbcc writepage ({‘dbname’ | dbid}, fileid, pageid, offset, length, data [, direct OR bufferpool])
Info:
Link: N/A

 

Command Name: dbcc readbytes
Syntax: dbcc readbytes
Info: No help available for Command Name: dbcc statement ‘readbytes’.
Link: N/A