Il team di sviluppo Microsoft ha introdotto in SQL Server 2005 diversi nuovi comandi DBCC. Alcuni di questi sono documentati, ma in generale esiste poca documentazione.

Per utilizzare i comandi non documentati occorre prima attivare il flag 2588 (2520 se utilizzate SQL Server 7.0 o 2000).
Come sempre, evitare di usare comandi non documentati in produzione: non è possibile sapere qual'è il comportamento "ufficiale" di un comando non documentato e quindi è fortemente sconsigliabile usarlo su dati reali.

• freesessioncache () -- no parameters
  
  
• requeststats ({clear} | {setfastdecayrate, rate} | {setslowdecayrate, rate})

• mapallocunit (I8AllocUnitId | {I4part, I2part})
  
  
• metadata ({'print' [, printopt = {0 |1}] | 'drop' | 'clone' [, '' | ....]}, {'object' [, 'type',...}, {Id | Name}, [{Ownerid | Ownername}], [{Dbid | Dbname}]])
  
  
• optimizer_whatif property, value
  
  
• persiststackhash (hashfile, BUFLATCH_TIMEOUT | ATTENTION | OUTOFLOCKS | LATCH_ATTN | OUTOFLOG | OUTOFMEM | SOS [, SKIPLAST | INCLUDELAST])
  
  
• semetadata (object id | name, index id | name [, partition id])

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 SQL Sever 2005.

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

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 has 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)
  

• 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

• 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)