DBCC COMMANDS WITH EXAMPLES

SQL Server – DBCC Commands

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

v  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 DROPCLEANBUFFERS

4. FREEPROCCACHE – Removes all elements from the procedure cache

DBCC FREEPROCCACHE

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

v  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 CONCURRENCYVIOLATION

2. 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 PROCCACHE

6. 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 USEROPTIONS

v  Validation 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_CONSTRAINTS

4. 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 CHECKFILEGROUP

6. 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’)

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