SQL SERVER DBCC Commands | Database console commands | Documented List

DBCC Commands

What is DBCC?

DBCC stands for Database Console Commands / Database Consistency Checker. 

DBCC commands are used for checking database integrity, troubleshooting issues, performance activity, and performing maintenance exercises on the database, tables, indexes, and filegroups, etc. DBCC commands help to understand what's happening inside SQL Server internally. DBCC commands are grouped into the following categories

• Maintenance:- Perform maintenance tasks on databases, indexes, or filegroups
• Informational:- Display SQL Server information
• Validation:- validate operations on databases, tables, indexes, catalogs, filegroups, or allocation of database pages.
• Miscellaneous:- enabling trace flags or removing a DLL from memory.

Apart from the documented DBCC commands, there are undocumented commands too but here we listing documented commands only.

First and most important command DBCC HELP which provides you the information on a specific DBCC command 

To List, all DBCC commands 

DBCC HELP ('?')

To check the syntax of DBCC command 

DBCC HELP ('command_name')

Eg: DBCC HELP ('CHECKDB')
dbcc CHECKDB 
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
            [ , [ MAXDOP = <dop> ] ]
        }
    ]

Following are the list of documented DBCC commands

Note: *  - Deprecated

Informational Statements

1. * DBCC SHOWCONTIG(table_name, index_name):- Provides fragmentation information for tables, views, or indexes.
   This command is deprecated and you should use sys.dm_db_index_physical_stats.
2. DBCC OPENTRAN:- Shows information about the oldest running transaction, oldest running distributed transaction, and oldest running non-distributed transaction.
3. DBCC SQLPERF(LOGSPACE):- Displays transaction log space usage statistics for all databases hosted by an instance.
4. DBCC INPUTBUFFER(SPID):- Returns the last command executed by a client.
5. DBCC OUTPUTBUFFER(SPID):- Returns the results of the last command executed by a client in hexadecimal and ASCII format.
6. DBCC TRACESTATUS:- Displays the active trace flags.
7. DBCC PROCCACHE:- Reports some basic statistics about the procedure cache.
8. DBCC USEROPTIONS:- Returns the active SET options for the current connections.
9. DBCC SHOW_STATISTICS(table_name, index_name):- Provides current query optimization statistics for a table or indexed view.

Validation Statements

1. DBCC CHECKALLOC('database_name'[, NOINDEX | REPAIR]):- Performs a consistency check of disk space allocation structures.
2. DBCC CHECKFILEGROUP([ {'filegroup_name' | filegroup_id} ] [, NOINDEX]):- Verifies the allocation and structural integrity of indexed views and tables in a specific filegroup.
3. DBCC CHECKCATALOG(db_name):- Checks catalog consistency of online databases.
4. DBCC CHECKIDENT('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ]):- Verifies and, if necessary, change the identity value for a specific table.
5. DBCC CHECKCONSTRAINTS( {'table_name' | tab_id | 'constraint_name' | constraint_id }):- Verifies the integrity of a specific constraint or all constraints on a table within the current database.
6. DBCC CHECKTABLE('Table'_name):- Performs physical and logical integrity checks on the table and all its nonclustered indexes (unless the NOINDEX option is used).
7. DBCC CHECKDB(('database_name'[, NOINDEX | REPAIR])):- Checks the physical and logical integrity of all objects in a specific database. It runs DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG.

Maintenance Commands

1. DBCC CLEANTABLE('database_name'|database_id, 'table_name'|table_id, [batch_size]):- Reclaims space from variable-length column (varchar,nvarchar, text, or ntext) that was dropped from the table.
2. * DBCC INDEXDEFRAG({database_id | database_name | 0}, {table_id | table_name}, {index_id | index_name}):- Defragments indexes.
   This command is deprecated and you should use REORGANIZE option of ALTER INDEX.
3. * DBCC DBREINDEX('table_name' [, index_name [, fillfactor ]]):- Rebuilds one or more indexes for a table.
   This command is deprecated and you should use REBUILD option of ALTER INDEX.
4. DBCC SHRINKDATABASE({ 'database_name' | database_id | 0 } [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ]):- Shrinks the size of all data and log files of the specified database.
5. DBCC DROPCLEANBUFFERS:- Cleans the memory of any buffered data.
6. DBCC SHRINKFILE({'Filename | File ID'} {[, emptyfile | [ [ , target_size] [, {NOTRUNCATE | TRUNCATEONLY } ]]}):- Shrinks a specified data or log file .
7. DBCC FREEPROCCACHE:- Clear an entire plan cache, remove specific plans from the plan cache by specifying a plan or SQL handle.
8. DBCC UPDATEUSAGE ({database_name}[, Table_name [,index_id]]):- Reports and corrects pages and row count inaccuracies in the catalog views.

Miscellaneous Commands

1. DBCC dllname (FREE):- Unloads the specified extended stored procedure dll from memory.
2. DBCC HELP:- Provides information on a specific DBCC command.
3. DBCC FLUSHAUTHCACHE:- This command works only in Azure SQL Database Empties the database authentication cache containing information about logins and firewall rules, for the current user database in SQL Database.
4. DBCC TRACEOFF:- Turns off the specified trace flags.
5. DBCC FREESESSIONCACHE:- Clears the cache used by the distributed queries.
6. DBCC TRACEON:- Turns on the specified trace flags.
7. DBCC FREESYSTEMCACHE:- Clears the unused cache entries from all caches include plan cache.
8. DBCC CLONEDATABASE(source_db,target_db):- Generates a schema-only clone of a database.

That's all about  SQL Server DBCC commands. Thanks for your time.

Let me know if you have any questions, suggestions, or comments.