SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
SQL Server 2005 DBCC Command Reference - Part 2 of 2
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Welcome back...

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 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.
Want More SQL Server Articles, Tips and Expert Advice?
If you liked this article please join SQL Server Club to receive our newsletter. Membership is free and you'll be first to hear about new articles, performance tuning tips written by practising DBAs, industry news, blogs, interviews and lots more.
Join SQL Server Club Today
Send to a Friend Printer Friendly Version
Part 1 || Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form