-- General Note:
-- Use 'WITH TABLERESULTS' option for most of these DBCC statements to get an output
-- that can be piped to a temporary table for analysis. This has been implemented for
-- most of the DBCC statements in SQL2000.

1. DBCC AUTOPILOT
        - Used by the index analysis tool in SQL70.
        - I haven't figured out how to use this though & what purpose it serves.
2. DBCC SHOWFILESTATS
        - Used in the SQL70 Enterprise Manager HTML code to get
        - the database files information. This one is pretty useful.
3. DBCC SHOWTABLEAFFINITY
        - To view table information like data, index, statistics pages etc.
        - This one is real cool. Used again in EM.
4. DBCC PERFMON
        - To see all the performance counters.
5. DBCC FLUSHPROCINDB
        - I don't know what this does. Probably flushed cached procs for the db.
6. DBCC DBTABLE(pubs)
7. DBCC DBINFO( pubs )
        - Gives low level information about the database files
8. DBCC BUFCOUNT([1..10])
        - This works till about 10 i think.
        - Gives the buffer chain information. Not much help for us. 
9. DBCC PSS
        - Documented in Technet.
10. DBCC DBREPAIR(@database, repairindex, @table, @indid)
        - Can be used to fix indexes on system tables only. Used in sp_fixindex.
11. DBCC DBREPAIR(@database, dropdb, noinit)
        - Can be used to drop a faulty database. Use this only in SQL6x. In SQL70 &
        - above, DROP DATABASE will suffice.
12. DBCC DBCONTROL(@database, online|offline)
        - Same as using sp_dboption.
13. DBCC LOCKOBJECTSCHEMA(@tab)
        - Can be used when modifying system table data.
        - Used in some upgrade scripts.
14. DBCC DBRECOVER(@database)
        - To run recovery on database.
15. DBCC REINDEXALL(@Database, 240)
        - I believe this reindexes the system tables.
        - May work for user tables also i think. Haven't tested this out.
16. DBCC ADDEXTENDEDPROC(function, dll) & DBCC DROPEXTENDEDPROC(function)
17. DBCC UPGRADEDB(@database)
        - Don't know what this does.
18. DBCC DETACHDB(@database)
19. DBCC DBREPAIR('', 'dropdevice', device_name, 1|0)
        - '1' will drop device & delete the physical file too.
        - '0' will just drop the device.
20. DBCC DBREPAIR(@database, markdirty)
        - Used when you rename the database to force sql server to
        - update its internal resources i guess.
21. DBCC PINTABLE(@database_id, @table_id) & UNPINTABLE(@database_id, @table_id)
        - Pin / unpin table in memory. Can be used in sql60/65 also i think.
22. DBCC bcptablock(@@dbid, @@tableid, 1/0)
        - Used by bcp for fast loading.
23. DBCC GAMINIT
        - This reinitializes the internal structures for each database.
24. DBCC DES
        - Prints the internal object descriptors.
        - Documented in technet kb article.
25. declare @dbcc_current_version integer
    dbcc getvalue('current_version')
    select @dbcc_current_version = @@error
    select @dbcc_current_version
        - This i believe gives some internal version number.
26. DBCC PGLINKAGE( dbid, start, number, printopt={0|1|2}, target, order={1|0})
        - Documented in technet kb article.
27. DBCC PrtIPage (dbID, TableID, IndexID, IndexPage)
28. DBCC LocateIndexPgs (dbID, TableID, Page, IndexID, IndexLevel)
29. DBCC PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )

--- SQL70
DBCC PAGE ( {dbid | dbname }, file#, page# [,printopt] [, cache]) 

dbid = ID of the database
dbname = Name of the database
file# = number of file containing page
page# = number of page within file
printopt = 0-2
---------------0(default) = print buffer & page headers
---------------1 = 0 + each row and row offset table
---------------2 = 0+ whole page and offset table
cache = 0/1
---------0 = print page as on disk
---------1(default) = print page as in cache (if present) or on disk (if not) 

30. DBCC LOGINFO
        - Provides virtual log file(s) details. Can be used to determine which virtual
        - log file contains the active portion of the transaction log. The Status value
        - is the one to watch out for. A value greater than zero means the corresponding
        - virtual log file is in use.
        - Please see the "sp_loginfo" SP under "SQL70Scripts\UtilitySps" page
31. DBCC CALLFULLTEXT
        - To invoke all the fulltext commands like reinitialize catalog etc.
32. DBCC STACKDUMP
        - Will generate a dump file on the server LOG directory. This will capture 
        - OS configuration, memory, a dump of all threads running on the server.
33. DBCC MEMORYSTATUS
        - Provides some memory related counters

This page was last updated on May 01, 2006 04:28 PM.