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.

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: dbcc addinstance
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: Checks the consistency of disk space allocation structures for a specified database.
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkalloc-transact-sql?view=sql-server-ver15

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

[ WITH NO_INFOMSGS ]
Info: Checks for catalog consistency within the specified database. The database must be online.
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkcatalog-transact-sql?view=sql-server-ver15

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

[ WITH
{ ALL_CONSTRAINTS | ALL_ERRORMSGS }
[ , [ NO_INFOMSGS ] ]
]
Info: Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkconstraints-transact-sql?view=sql-server-ver15

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: Checks the logical and physical integrity of all the objects in the specified database
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

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 cleanupconversations
Syntax: dbcc cleanupconversations [({‘dbname’ | dbid})]
Info:
Link: N/A

Command Name: dbcc cleanup_xtp
Syntax: dbcc cleanup_xtp
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 clonedatabase
Syntax: dbcc clonedatabase (‘source_database’ ,’target_database’)
[ WITH
{
[ NO_STATISTICS ]
[ , [ NO_QUERYSTORE ] ]
[ , [ BACKUP_CLONEDB ] ]
[ , [ { VERIFY_CLONEDB | SERVICEBROKER } ] ]
}
]
Info: Generates a schema-only clone of a database
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-ver15

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 flushauthcache
Syntax: dbcc flushauthcache
Info: Empties the database authentication cache containing information about logins and firewall rules (Azure SQL Database and Azure SQL Managed Instance)
Link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-flushauthcache-transact-sql?view=azuresqldb-current

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: https://support.microsoft.com/en-us/help/907877/how-to-use-the-dbcc-memorystatus-command-to-monitor-memory-usage-on-sq

Command Name: dbcc memorystatus_ex
Syntax: dbcc memorystatus_ex
Info: Similar to dbcc memorystatus
Link: N/A

Command Name: dbcc multi_instance_mem_rg
Syntax: dbcc multi_instance_mem_rg(1,1,1,1)
Info: Running dbcc multi_instance_mem_rg(1,1,1,1) on SQL Server 2019 RTM, build 15.0.2000.5 generates a stack dump, so be careful!
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: Run dbcc optimizer_whatif(0) to get current status
Link: http://www.sqlservice.se/trace-flag-undocumented-commands/ and https://www.mssqltips.com/sqlservertip/3303/usage-of-dbcc-optimizerwhatif-for-sql-server-query-tuning/

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 pfs_close_file
Syntax: dbcc pfs_close_file (pfs_file_path)
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 remoterowgroup
Syntax: dbcc remoterowgroup (rowsetid, rowgroupid, fileid, azure storage container id)
Info:
Link: N/A

Command Name: dbcc resource
Syntax: dbcc resource
Info: Outputs misc info about the instance settings and configurations
Link: N/A

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

Command Name: dbcc ruleon
Syntax: dbcc ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
Info: Turns on optimizer rules
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: Shows active optimizer rules
Link: https://www.mssqltips.com/sqlservertip/4175/disabling-sql-server-optimizer-rules-with-queryruleoff/

Command Name: dbcc showonrules
Syntax: dbcc showonrules
Info: Shows inactive optimizer rules
Link: https://www.mssqltips.com/sqlservertip/4175/disabling-sql-server-optimizer-rules-with-queryruleoff/

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
Syntax: dbcc thaw_io (dbname | dbid)
Info:
Link: N/A

Command Name: dbcc throttle_io
Syntax: dbcc throttle_io (io_rate)
Info: Limits the IO to the value of io_rate (in Megabytes per Second)
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

Kontakta mig

Kontakta mig

Updated: Microsoft SQL Server DBCC Commands list

Microsoft SQL Server DBCC Commands list

Ställ en fråga

Kontakta mig

Updated: Microsoft SQL Server DBCC Commands list

Microsoft SQL Server DBCC Commands list

Läs liknande inlägg