DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.
The DBCC Commands broadly falls into four categories:
Maintenance
Informational
Validation
Miscellaneous
Maintenance Commands
Performs maintenance tasks on a database, index, or filegroup.
1. CLEANTABLE - Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE ('AdventureWorks','Person.Contact',0)2. DBREINDEX - Builds one or more indexes for the table in the specified database.
(Will be removed in the future version, use ALTER INDEX instead) USE AdventureWorks
DBCC DBREINDEX ('Person.Contact','PK_Contact_ContactID',80)3.
DROPCLEANBUFFERS - Removes all clean buffers from buffer pool.
DBCC DROPCLEANBUFFERS4. FREEPROCCACHE - Removes all elements from the procedure cache
DBCC FREEPROCCACHE5. INDEXDEFRAG - Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG ('AdventureWorks', 'Person.Address', PK_Address_AddressID)6. SHRINKDATABASE - Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE ('AdventureWorks', 10)7. SHRINKFILE - Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file
to other files in the same filegroup, allowing the file to be removed from the database.
USE AdventureWorks;
- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)8. UPDATEUSAGE - Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks)Informational Commands Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION - is maintained for backward compatibility. It runs but returns no data.
DBCC CONCURRENCYVIOLATION2. INPUTBUFFER - Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
DBCC INPUTBUFFER (52)3. OPENTRAN - Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.
DBCC OPENTRAN;4. OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
DBCC OUTPUTBUFFER (52)5. PROCCACHE - Displays information in a table format about the procedure cache.
DBCC PROCCACHE6. SHOW_STATISTICS - Displays the current distribution statistics for the specified target on the specified table USE AdventureWorks
DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid)7. SHOWCONTIG
- Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG ('HumanResources.Employee');8. SQLPERF - Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
DBCC SQLPERF(LOGSPACE)9. TRACESTATUS - Displays the status of trace flags.
DBCC TRACESTATUS(-1)10. USEROPTIONS - Returns the SET options active
(set) for the current connection.
DBCC USEROPTIONSValidation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)2. CHECKCATALOG - Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)3. CHECKCONSTRAINTS - Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS4. CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)5. CHECKFILEGROUP - Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP6. CHECKIDENT - Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT ('HumanResources.Employee')7. CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE ('HumanResources.Employee')Miscellaneous Commands Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) - Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)2. TRACEOFF - Disables the specified trace flags.
DBCC TRACEOFF (3205)3. HELP - Returns syntax information for the specified DBCC command.
- List all the DBCC commands
DBCC HELP ('?')
- Show the Syntax for a given DBCC commnad
DBCC HELP ('checkcatalog')4. TRACEON - Enables the specified trace flags.
DBCC TRACEON (3205)