The DBCC (database Base consistency Checker, DB consistency checker) commands are used to verify database integrity, find errors, and analyze system usage.
1. DBCC Checkalloc
Checks the consistency of the disk space allocation structure for the specified database.
Grammar:
DBCCCheckalloc[(database_name | database_id | 0 [, NOINDEX |, {repair_allow_data_loss | Repair_fast | Repair_rebuild}] ) [With {[All_errormsgs] [, No_infomsgs] [, TABLOCK] [, Estimateonly] } ]]
Example:
DBCC Checkalloc ('Portal')
2. DBCC Showcontig
Displays fragmentation information for the data and indexes of the specified table.
Grammar:
DBCCShowcontig[({table_name | table_id | view_name | view_id} [, Index_name | index_id] ) ] [with {[, [All_indexes] ] [, [Tableresults] ] [, [FAST] ] [, [All_levels] ] [no_infomsgs] } ]
Example:
DBCC Showcontig ('Category','pk_category')
DECLARE @TableID INT,@IndexID INTSET @TableID = object_id('Category')SELECT @IndexID =index_id fromSys.indexesWHERE object_id = @TableID andName= 'pk_category'DBCCShowcontig (@TableID,@IndexID)
SQL Server series: DBCC commands