DBCC commands that are commonly used in SQL Server

The DBCC (Database consistenecy Checker) command is a utility command set that examines the logical consistency and physical consistency of a database.

Mainly divided into four categories:

Maintenance statement:
DBCC Dbreindex: Rebuilding one or more indexes in a specified table
DBCC Dbreindex
(' Database.owner.table_name '
[, Index_name
[, FillFactor]
]
) [With NO_INFOMSGS]

DBCC Indexdefrag: Defragment the specified table or attempted clustered and secondary index fragments

DBCC Indexdefrag
({database_name database_id 0}
, {table_name table_id ' view_name ' view_id}
, {index_name index_id}
) [With NO_INFOMSGS]

DBCC shrinkdatabase: Narrowing the size of the data file for the specified database

DBCC Shrinkdatabase
(database_name [, target_percent]
[, {notruncate truncateonly}]
)

DBCC shrinkfile: Shrinking data files or log files of a specified database large size

DBCC Shrinkfile
({file_name file_id}
{[, target_size]
[, {emptyfile notruncate truncateonly}]
}
)

DBCC updateusage: Incorrect content for reporting and correcting sysindexes tables

DBCC updateusage
({' database_name ' 0}
[, {' table_name ' view_name '}
[, {index_id ' index_name '}]]
)
[with [Count_rows] [, NO_INFOMSGS]
]

Miscellaneous statements:

DBCC Traceon/off: Turn off the specified trace flag

DBCC Traceon/off (trace# [,... n])

Status statement:

DBCC Showcontig: Fragment information showing data and indexes for the specified table (use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to shred the indexes in the database)

Sheet finishing)

DBCC Showcontig
[({table_name table_id view_name view_id}
[, Index_name index_id]
)
]
[With {all_indexes
FAST [, All_indexes]
Tableresults [, {all_indexes}]
[, {FAST all_levels}]
}
]

DBCC Show_statistics: Displays the forward distribution statistics for the specified target on the specified table

DBCC show_statistics (table, target)

Validation statement:

DBCC Checkalloc: Checking the consistency of the disk space allocation structure of the specified database

DBCC Checkalloc
(' database_name '
[, NOINDEX

{Repair_allow_data_loss
Repair_fas T
Repair_rebuild
} ]
) [With {[All_errormsgs no_infomsgs]
[, [TABLOCK]]
[, [Estimateonly]]
}
]

DBCC CheckCatalog: Checks for consistency between system tables and system tables in the specified database

DBCC CheckCatalog
(' database_name '
) [With NO_INFOMSGS]

DBCC checkconstraints: Check the integrity of the specified constraint or all constraints on the specified table

DBCC checkconstraints
[(' table_name ' constraint_name ')
)]

[With {All_errormsgs all_constraints}]

DBCC CHECKDB: Checks allocation and structural integrity of all objects in the specified database

DBCC CHECKDB
(' database_name '
[, NOINDEX
{Repair_allow_data_loss
Repair_fast
Repair_rebuild
} ]
) [with {[ALL_ERRORMSGS]
[, [NO_INFOMSGS]]
[, [TABLOCK]]
[, [Estimateonly]]
[, [Physical_only]]
}
]

DBCC CheckFilegroup: Checks for allocation and structural integrity of all tables in the specified filegroup (current database)

DBCC CheckFilegroup
([{' Filegroup ' filegroup_id}]
[, NOINDEX]
) [With {[All_errormsgs no_infomsgs]
[, [TABLOCK]]
[, [Estimateonly]]
}
]

DBCC checktable: Checks the integrity of data, indexes, and text,ntext and image pages in the specified table or indexed view

DBCC checktable
(' table_name ' view_name '
[, NOINDEX
index_id
{Repair_allow_data_loss
Repair_fast
Repair_rebuild}
]
) [With {[All_errormsgs no_infomsgs]
[, [TABLOCK]]
[, [Estimateonly]]
[, [Physical_only]]
}
]