SQL Server 2005 DBCC Command Quick Reference

New, undocumented and retired DBCC Commands in SQL Server 2005

Seven new DBCC commands have been introduced by Microsoft's SQL Server development team.

Unfortunately little or no documentation is available on the new commands listed below, though some of them may be documented in the RTM release.

Those that are listed as being documented do not require a trace flag to be set before using them.

However, to use the undocumented commands, you will need to turn on trace flag 2588.

This has changed since SQL Server 7.0/2000, where the trace flag was 2520.

Please note that the following is a result of investigations with the beta 2 release of SQL Server 2005, the final RTM release may differ slightly.

As always, never use an undocumented DBCC command on a production server unless advised by Microsoft, and never use a documented one unless you understand how it may affect the performance of your server.

DBCC commands new to SQL Server 2005

Documented new commands

freesessioncache () -- no parameters

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

Undocumented new commands

mapallocunit (I8AllocUnitId | {I4part, I2part})

' | ....]}, {'object' [, 'type',...}, {Id | Name}, [{Ownerid | Ownername}], [{Dbid | Dbname}]])

optimizer_whatif property, value

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

DBCC commands altered since SQL Server 2000

The following is presented as a list of pairs of commands. The first command is the old syntax, as used in SQL Server 2000.

The second of each pair is the altered syntax new to SQL Server 2005.

In most cases the commands have been extended to take advantage of passing an object ID instead of a name, but if your scripts use any of these commands, it's probably worth checking them out before you migrate to SS2K5.

2000 : checkalloc [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

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

Changes : SQL Server 2005 now accepts the dbid as well as the dbname

2000 : checkdb [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]

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

Changes : SQL Server 2005 now accepts the dbid as well as the dbname

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

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

Changes :

Although the syntax is identical for SQL Server 2000 and 2005, there is a subtle change in the behaviour of this command.

In SQL Server 7.0 and 2000, running checkident would cause the identity column to be re-seeded, even if the table was empty.

In SQL Server 2005, if the table is empty when dbcc checkident is run, the reseed value will be ignored.

2000 : dbrepair ('dbname', DROPDB [, NOINIT])

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

Changes : dropdevice syntax changed ; markdirty and repairindex options added

NB : It seems odd that this command has been extended with this release, as in the SQL Server 2005 setup help file, setupsql9.chm, it states that DROP DATABASE should be used instead of this command. It was included in SQL Server 2000 for backward compatibility only.

2000 : indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})

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

Changes : An extra optional parameter has been added, partition_number

2000 : inputbuffer (spid)

2005 : inputbuffer (spid, [batchid])

Changes : An extra optional parameter has been added, batch_id

2000 : outputbuffer (spid)

2005 : outputbuffer (spid, [batchid])

Changes : An extra optional parameter has been added, batch_id

2000 : proccache

2005 : proccache ([compplan_ticks_threshold])

Changes : An optional parameter has been added, compplan_ticks_threshold

2000 : sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})

2005 : sqlperf (LOGSPACE | IOSTATS | NETSTATS | RASTATS [, CLEAR]} | [THREADS] )

Changes : As for 2000, but LRUSTATS has been removed as an option.

NB : Microsoft only document the LOGSPACE parameter of this command - use any others at your own discretion.

2000 : updateusage ({'database_name'| 0} [, 'table_name' [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]

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

Changes : Can now specify db_id, table_id, or the index name as parameters, instead of just the db/table/index name.

Also note that there is a problem with the output generated by the dbcc showcontig command under certain conditions in the beta version of SQL Server 2005, where more than one block of information per index is generated for tables that contain text columns.

DBCC commands retired since SQL Server 2000

Many of us have used them at one time or another and a few might even depend upon them.

However, we can't say we have not been warned, and Microsoft have finally retired a whole raft of dbcc commands in SQL Server 2005.

Most of these were not particularly useful, but thoughtfully retained right up to SQL Server 2000 for backward compatibility with SQL Server 6.5 and earlier scripts.

The following dbcc commands are now dead and buried from SQL Server 2005 onwards:

adduserobject (name)

balancefactor (variance_percent)

bufcount [(number_of_buffers)]

cacheprofile [( {actionid} [, bucketid])

checkdbts (dbid, newTimestamp)]

des [( {'dbname' | dbid} [, {'objname' | objid} ])]

dropuserobject ('object_name')

getvalue (name)

iotrace ( { 'dbname' | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

lockobjectschema ('object_name')

matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})

memospy

memusage ([IDS | NAMES], [Number of rows to output])

monitorevents ('sink' [, 'filter-expression'])

newalloc (previously retired, use of checkalloc recommended instead)

perflog

pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})

procbuf [({'dbname' | dbid}[, {'objname' | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]

rebuild_log (dbname [, filename])

row_lock (dbid, tableid, set) - Not Needed

shrinkdb (previously retired, use of shrinkdatabase recommended instead)

tab ( dbid, objid )

tape_control {'query' | 'release'}[,('\\.\tape')]

textall [({'database_name'|database_id}[, 'FULL' | FAST] )]

textalloc ({'table_name'|table_id}[, 'FULL' | FAST])

upgradedb (db)

usagegovernor (command, value)

wakeup (spid)

DBCC commands included in SQL Server 2005, which will be retired at a later date

dbreindex

This will be replaced with the REBUILD option of the ALTER INDEX statement.

indexdefrag

This will be replaced with the REORGANIZE option of the ALTER INDEX statement.

showcontig

This command will be replace by the system function fn_indexinfo

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 SP's to profile.
Use 'dbcc icecapquery (icecapall)' to profile all SP's.

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

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:

SQL Server 2005 Express Edition

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 http://www.microsoft.com/sql/

SQL Server 2005 Setup Help (c) Microsoft Corporation 2004.

The information about future discontinued DBCC commands was sourced from Microsoft's SQL Server 2005 setup help file.
It is recommended reading for anyone who writes commercial database software that depends upon the lower-level functionality provided by SQL Server, as it includes details of discontinued commands and configuration options.

This document can be found at C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Help\1033\setupsql9.chm,

after installing SQL Server 2005 Express Edition to the default installation directory.
Search on "dbcc" to find this information.

Jon Reade November 2004

SQL Server 2005 DBCC Command Quick Reference