SQL SERVER – DBCC commands List – documented and undocumented

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises.
To learn about all the DBCC commands run following script in query analyzer.
DBCC TRACEON(2520)
DBCC HELP (‘?’)
GO
To learn about syntax of an individual DBCC command run following script in query analyzer.
DBCC HELP(<command>)
GO
Following is the list of all the DBCC commands and their syntax. List contains all documented and undocumented DBCC commands.
DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname
, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

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

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkalloc [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

DBCC checkcatalog [(‘database_name’)] [WITH NO_INFOMSGS]

DBCC checkconstraints [( ‘tab_name’ | tab_id | ‘constraint_name’ | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

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

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkfilegroup [( [ {‘filegroup_name’ | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

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

DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])

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

DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id,[batch_size])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches (‘database_name’|database_id,
‘table_name’|table_id, ‘index_name’|index_id)

DBCC collectstats (on | off)

DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC config

DBCC cursorstats ([spid [,’clear’]])

DBCC dbinfo [(‘dbname’)]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

DBCC dbtable [({‘dbname’ | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {‘dbname’ | dbid} [, {‘objname’ | objid} ])]

DBCC detachdb [( ‘dbname’ )]

DBCC dropcleanbuffers

DBCC dropextendedproc (function_name)

DBCC dropuserobject (‘object_name’)

DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number}
| {‘CLEAR’, exception_number})

DBCC errorlog

DBCC extentinfo [({‘database_name’| dbid | 0}
[,{‘table_name’ | table_id} [, {‘index_name’ | index_id | -1}]])]

DBCC fileheader [( {‘dbname’ | dbid} [, fileid])

DBCC fixallocation [({‘ADD’ | ‘REMOVE’},
{‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}
, filenum, pagenum [, objectid, indid])

DBCC flush (‘data’ | ‘log’, dbid)

DBCC flushprocindb (database)

DBCC free dll_name (FREE)

DBCC freeproccache

dbcc freeze_io (db)

dbcc getvalue (name)

dbcc 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.

dbcc incrementinstance (objectname, countername, instancename, value)

dbcc ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )

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

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

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

DBCC latch ( address [, ‘owners’] [, ‘stackdumps’])

DBCC lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}] |
[{‘STALLREPORTTHESHOLD’, stallthreshold}])

DBCC lockobjectschema (‘object_name’)

DBCC log ([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’]]…]]])

DBCC loginfo [({‘database_name’ | dbid})]

DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

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

DBCC monitorevents (‘sink’ [, ‘filter-expression’])

DBCC newalloc – please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC opentran [({‘dbname’| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

DBCC outputbuffer (spid)

DBCC page ( {‘dbname’ | dbid}, filenum, pagenum
[, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

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

DBCC pintable (database_id, table_id)

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

DBCC proccache

DBCC prtipage (dbid, objid, indexid [, [{{level, 0}
| {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid
, formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) – Not Needed

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

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

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC show_statistics (‘table_name’, ‘target_name’)

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

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage
[, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC shrinkfile ({fileid | ‘filename’}, [compress_size
[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

DBCC sqlmgrstats

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

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {‘query’ | ‘release’}[,(‘.tape’)]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({‘database_name’|database_id}[, ‘FULL’ | FAST] )]

DBCC textalloc ({‘table_name’|table_id}[, ‘FULL’ | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum … ] )]

DBCC traceon [( tracenum [, tracenum … ] )]

DBCC tracestatus (trace# [, …trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db) DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions DBCC wakeup (spid)

DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL, Extreme Expert

,
Previous Post
SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Photo
Next Post
SQL SERVER – Binary Sequence Generator – Truth Table Generator

Related Posts

20 Comments. Leave new

  • No reference to which version of SQL these commands are for: SQL 7 (I doubt it ), 2000 or 2005?

    I’m guessing 2005, given the date of the post, and the things like DBCC useplan, but confirmation would be useful.

    Also, I don’t see reference to what should (imho) be a standard disclaimer around undocumented code – i.e. “Use at your own risk, since MS undocumented code and features are also, typically, unsupported, and liable to change in hotfixes (less likely), service packs, and new versions (more likely). do not build systems based on these – use them, when needed, for information and analysis”. again, thats imho

    Reply
  • where Can i get the document containing the DBCC Command with Detail.

    Thanks in Adavance.

    Reply
  • Hi,

    I am Sudeepta from Bangalore. While I was going through the database used in our Support environment, I found that the size of the .ldf file is above 10GB. I manage to remove such a big log file to save my disk space.

    My question is: “How to read the database .ldf file?” Currently I am using a SQL 2000 Database on Windows 2000 server.

    I have tried to search the same in Google, however, didn’t find a satisfied answer.

    I believe, that you will reply me with a solution…

    Thanks a lot for all those valuable articles……

    Thanks & Regards,
    Sudeepta.

    Reply
  • Is this trace flag valid for SQL Server 2005 also

    DBCC TRACEON(2520)
    DBCC HELP (‘?’)
    GO

    Using this on my 2005 instance (build 3152), I dont see all the undocumented commands you have above. Is there another trace flag along with this?

    Reply
  • Hi,

    1. DBCC stands for DataBase Consistency Checker.
    2. DBCC stands for DataBase Console Commands.

    Which is the correct full form of DBCC. In SQL Server Books online it is stated that, the Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server 2000.

    Reply
  • as far as i can see, these are sql server 2000 commands.
    a headline would have been helpfull.
    regards
    raimund

    Reply
  • do i require the specific set of permission to run the DBCC commands?

    Reply
  • Hi, i would like to know every dbcc command how it work and full document in detail

    Reply
  • hi i am a student i was learning ms sql 2000 actually i was looking for a complete set of commands being used in the query analyzer window to work with the databases i would be glad if i can get something like that

    Reply
  • Use DBCC TRACEON(2588) for SQL Server 2005 & 2008.

    Reply
  • Hello Dave,

    I wonder if there is a way to retrieve which sql commands were executed within the database during the last day?
    I guess it has something to do with the LSN, which I can get through dbcc log.

    I really appreciate your blog. Thank you very much.

    Reply
  • How do you list or show all constraints on a SQL Server 2005 database?

    Thanks.

    Reply
  • @Lisa

    SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS

    Is that what you want?

    Reply
  • Hi,

    Thanks for the information!

    Do you know how we can grant a user who is db_owner in a database and has the dbcreator server role rights to run DBCC detachdb [( ‘dbname’ )]?

    TIA,
    Dave

    Reply
  • i have one Doubt how much time connect DB use my .net Page it is possible means please Help any one.

    Reply
  • i have one Doubt how much time connect DB use my .net Page it is possible means please Help any one. using sqlserver

    Reply
  • how DBCC CACHESTATS commnad will work?

    Reply
  • When use DBCC CheckDB then need to run again CHECKCONSTRAINTS and CHECKCATALOG in sql server 2005 and SQL server 2008 ? Because my one friends not agree , he told three command run

    Reply
  • Hi ,

    when i run dbcc check db my sql services are restarting unexpectedly, could anyone aware of this issue. we are using sql 2012 sp3

    regards
    arshad

    Reply

Leave a Reply

Menu