DBCC Commands Included in SQL Server 2005, which will be Retired at a Later Date

Complete List of Documented SQL Server 2005 DBCC Commands

• checkalloc [('dbname'|dbid[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

• checkcatalog [('dbname'|dbid)] [WITH NO_INFOMSGS]

• checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

• checkdb [('dbname | dbid'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY] [, TABLOCK]]

• checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY] [, ESTIMATEONLY][, TABLOCK]]

• checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

• checktable ('table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY] [, ESTIMATEONLY][, TABLOCK]]

• cleantable ('dbname'|dbid, 'table_name'|table_id [, batch_size])

• concurrencyviolation (reset | display | startlog | stoplog)

• dbreindex ('table_name' [, index_name [, fillfactor]]) [WITH NO_INFOMSGS]

• dbrepair ('dbname', markdirty | {dropdevice, int} | {repairindex, int, int})

• dropcleanbuffers

• free dll_name (FREE) e.g. DBCC xp_sample (FREE)

• freeproccache

• freesessioncache

• help ('dbcc_command' | '?')

• indexdefrag ({dbname | dbid | 0}, {tableid | tablename} [, {indid | indname} [, partition_number]])

• inputbuffer (spid, [batchid])

• opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

• outputbuffer (spid, [batchid])

• perfmon

• pintable (database_id, table_id)

• proccache ([compplan_ticks_threshold])

• requeststats ({clear} | {setfastdecayrate, rate} | {setslowdecayrate, rate})

• show_statistics ('table_name'[, 'target_name'])

• showcontig ([table_id | table_name [, index_id | index_name]] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

• shrinkdatabase ({'dbname'|dbid}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])

• shrinkfile ({fileid | 'filename'} {[, EMPTYFILE] | [[, compress_size] [, {NOTRUNCATE | TRUNCATEONLY}]]})

• sqlperf (LOGSPACE)

• traceoff [( tracenum [, tracenum ... ] )]

• traceon [( tracenum [, tracenum ... ] )]

• tracestatus (trace# [, ...trace#])

• unpintable (dbid, table_id)

• updateusage ({'dbname' | dbid | 0} [, {'table_name' | table_id} [, {index_id | 'index_name'}]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]
• useroptions

Complete List of Undocumented SQL Server 2005 DBCC Commands

• activecursors [(spid)]

• addextendedproc (function_name, dll_name)

• addinstance (objectname, instancename)

• auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid, objname, servername, providername)

• autopilot (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])

• buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

• bytes ( startaddress, length )

• cacheprofile ( actionid [, bucketid])

• cachestats

• callfulltext - system sp use only

• checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])

• clearspacecaches ('dbname'|dbid, 'table_name'|table_id, 'index_name'|index_id [, partition_number])

• collectstats (on | off)

• cursorstats ([spid [,'clear']])

• dbrecover (dbname [, IgnoreErrors])

• dbreindexall (dbname|dbid[, type_bitmap])

• debugbreak

• deleteinstance (objectname, instancename)

• detachdb ( 'dbname' [, fKeep_Fulltext_Index_File (0 | 1)] )

• dropextendedproc (function_name)

• config

• dbinfo [('dbname')]

• dbtable [({'dbname' | dbid})]

• lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}]|[{'STALLREPORTTHESHOLD', stallthreshold}])

• log (dbname | dbid [,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']|['extent','x:y']|['pageid','x:y']| ['objid',{x,'y'}]|['logrecs',{'lop'|op}...]|['output',x,['filename','x']]...]]])

• page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ]) pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

• resource

• dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} | {'CLEAR', exception_number})

• errorlog

• extentinfo [({'dbname'| dbid | 0} [, {'tablename' | tableid} [, {'indexname' | indexid | -1} [, partition_number]]])]

• fileheader [( {'dbname' | dbid} [, fileid])

• fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}, filenum, pagenum [, objectid, indexid, partitionid, allocUnitId])

• flush ('data' | 'log', dbname | dbid)

• flushprocindb (dbid)

• freeze_io (dbname | dbid)

• icecapquery ('dbname' [, stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)]])
  • Use 'dbcc icecapquery (printlist)' to see list of stored procs to profile.
  • Use 'dbcc icecapquery (icecapall)' to profile all stored procs.

• incrementinstance (objectname, countername, instancename, value)

• ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } [, partition_number] )

• invalidate_textptr (textptr)

• invalidate_textptr_objid (objid) latch ( address [, 'owners'] [, 'stackdumps'])

• loginfo [({'dbname' | dbid})]

• mapallocunit (I8AllocUnitId | {I4part, I2part})

• memobjlist [(memory object)]

• memorymap

• memorystatus

• metadata ({'print' [, printopt = {0 |1}] | 'drop' | 'clone' [, '' | ....]}, {'object' [, 'type',...}, {Id | Name}, [{Ownerid | Ownername}], [{Dbid | Dbname}]])

• no_textptr (table_id , max_inline)

• optimizer_whatif property, value

• persiststackhash (hashfile, BUFLATCH_TIMEOUT | ATTENTION | OUTOFLOCKS | LATCH_ATTN | OUTOFLOG | OUTOFMEM | SOS [, SKIPLAST | INCLUDELAST])

• prtipage (dbname | dbid, objid | objname, indexid | indexname [, partition_number [, level]]).
  • No partition specified uses the first partition.
  • No level specified prints root page.

• readpage ({'dbname'|dbid}, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

• renamecolumn (object_name, old_name, new_name)

• ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

• ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)

• semetadata (object id | name, index id | name [, partition id])

• setcpuweight (weight)

• setinstance (objectname, countername, instancename, value)

• setioweight (weight)

• showdbaffinity

• showfilestats [(file_num)]

• showoffrules

• showonrules

• showtableaffinity (table_id | table_name [, partition_number])

• showtext ('dbname' | dbid, {textpointer | {fileid, pageid, slotid [,option]}})

• showweights

• sqlmgrstats

• stackdump [( {uid[, spid [, batchid [, ecid]]} | {threadId, 'THREADID'}] )] tec [( uid[, spid[, batchid[, ecid]] )]

• thaw_io (dbname | dbid)

• useplan [(number_of_plan)]

• writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data)

Acknowledgements and References

• Except where noted below, the above investigation was carried out on the Beta 2 release of SQL Server 2005 Express Edition. At the time of writing (November 2004) this product was available as a free download at www.microsoft.com/sql.

• SQL Server 2005 Setup Help © Microsoft Corporation 2004.

• SQL Server 2005 Express Edition.