Database Console Commands (DBCC) in SQL Server

DBCC is very familiar Transact-SQL command which is mostly known as Database Console Commands or Database Consistency Checker. The main purpose of this command is to provide a valuable insight within a SQL Server database.  We can say that DBCC is a T-SQL command which is used to check the physical and logical consistency of a Microsoft SQL Server database. If you are facing any performance related problems or issues then this command is the best option to troubleshoot them. Microsoft has been grouped this command in to four categories as given below-

Command category	Perform
Maintenance	Maintenance tasks on a database to perform maintenance activities on the database such as shrinking a file or index or file groups etc.
Miscellaneous	Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational	Tasks that gather and display various types of information.
Validation	Validation operations to perform on a database, table, index, catalog, file group, or allocation of database pages.

Parameters - DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Maintenance DBCC Commands

In this category DBCC Maintenance commands are used to allow Maintenance tasks on a database to perform maintenance activities on the database such as shrinking a file or index or file groups etc.

The mostly commonly used maintenance commands are defining below:

DBCC CLEANTABLE – The executer of this command must own the table or indexed view, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

DBCC CLEANTABLE recovers space after a variable-length column is dropped. As we know that a variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.

Important Points about DBCC CLEANTABLE –

• This command does not reclaim space after a fixed-length column is dropped.
• If the dropped columns were stored in-row, DBCC CLEANTABLE reclaims space from the IN_ROW_DATA allocation unit of the table.
• If the columns were stored off-row, space is reclaimed from either the ROW_OVERFLOW_DATA or the LOB_DATA allocation unit depending on the data type of the dropped column.
• If reclaiming space from a ROW_OVERFLOW_DATA or LOB_DATA page results in an empty page, DBCC CLEANTABLE removes the page. DBCC CLEANTABLE runs as one or more transactions.
• If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation.
• For some large tables, the length of the single transaction and the log space required may be too much.
• If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows.
• DBCC CLEANTABLE cannot be run as a transaction inside another transaction. This operation is fully logged.
• DBCC CLEANTABLE is not supported for use on system tables, temporary tables, or the xVelocity memory optimized columnstore index portion of a table.

Best Practices

It is highly recommended that DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after we make significant changes to variable-length columns in a table or indexed view and we have needed to immediately reclaim the unused space. Alternatively, we can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.