SQL Server DBCC Commands FAQ

In SQL Server, DBCC is the acronym for Database Console Command. These commands are intended to check for database, table or index corruption as well as shrink files, check input buffers, clear procedure cache and more.

The most used DBCC commands are DBCC CHECKDB, DBCC CHECKTABLE, DBCC OPENTRAN, DBCC SHRINKFILE, DBCC INPUTBUFFER, DBCC FREEPROCCACHE, etc. Each of these commands performs a specific task such as checking for database corruption, open transactions or shrinking a database file.

DBCC CHECKDB checks the consistency of a SQL Server database to ensure there is no corruption at the data or index page level. There are parameters that can be passed to this command to correct corruption including correcting the corruption at the price of losing data.

Additional Information:
– SQL Server DBCC CHECKDB Overview
– SQL Server DBCC CHECKDB with MAXDOP
– SQL Server DBCC CHECKDB, CHECKCATALOG and CHECKALLOC for VLDBs
– Importance of Performing DBCC CHECKDB on all SQL Server Databases
– SQL Server Database Corruption and Impact of running CHECKDB repair with allow data loss
– SQL Server DBCC CHECKDB with DATA_PURITY command
– Generate HTML Email for SQL Server Database Consistency Check Errors

DBCC CHECKDB should be run after each backup occurs on a non-production environment to offload the processing from production and to proactively check for corruption. Here are scripts to run DBCC CHECKDB on another server.

DBCC CHECKTABLE checks the consistency of a specific SQL Server user defined table in a user defined database for corruption. DBCC CHECKTABLE can be run on larger tables, if there is not sufficient time to run DBCC CHECKDB or after correcting corruption on a specific table. However, it is prudent to run DBCC CHECKDB to ensure there is not corruption on every object after corrective measures have been taken to correct database corruption.

DBCC CHECKALLOC checks the internal allocation structures for a user defined SQL Server database. DBCC CHECKALLOC is included as a check when DBCC CHECKDB is run.

DBCC CHECKCATALOG checks the consistency of the system catalog (i.e. objects). DBCC CATALOG is included as a check when DBCC CHECKDB is run.

DBCC PAGE is used to review the contents of SQL Server data and index pages. This is generally used when troubleshooting corruption.

DBCC WRITEPAGE is a command to test and correct SQL Server database corruption. This command can cause corruption and should only be used in development or test environments.

DBCC CHECKPRIMARYFILE reads the meta data from the primary database data file on disk such as the database name, logical file name, physical file name, etc. One reason to do so is to use the output from this command to attach databases.

DBCC SHRINKFILE is used on a SQL Server database to shrink an existing database or transaction log file with the ability to free up disk space on the drive.

DBCC CLONEDATABASE creates a copy of an existing SQL Server database with only the objects and statistics. No data is included so the cloned database is generally a fraction of the size of the original version. DBCC CLONEDATABASE is helpful to use when trying to perform query tuning or move the database structure to a development or test environment without the data.

DBCC DROPCLEANBUFFERS is a means to clear cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.

DBCC FREEPROCCACHE is a means to clear the procedure cache for testing purposes in a SQL Server development or test environment. This should not be run in a production environment.

DBCC INPUTBUFFER with the System Process ID passed to it displays the last run command for that specific System Process ID in SQL Server. Depending on the size of the code issued, this command may not return all code run. You can also search for the oldest active SQL Server transaction. An alternative to DBCC INPUTBUFFER is to use the fn_get_sql function.

DBCC OPTIMIZER_WHATIF in SQL Server provides the ability to perform query plan simulation for differing hardware configurations between production and development as an example.

DBCC UPDATEUSAGE in SQL Server updates the internal page counts that sometimes become inaccurate with a large data load or deletion. DBCC UPDATEUSAGE can help the SQL Server optimizer obtain accurate data for query plans.

DBCC SHOW_STATISTICS provides statistics for a particular SQL Server table including the most recent UPDATE STATISTICS, row count, histogram steps, etc. However, incorrect table statistics can cause slow query execution, which is why current statistics are important.

DBCC SQLPERF(logspace) reports the SQL Server transaction log usage including virtual log file management.

DBCC LOGINFO reports on the virtual logs in the SQL Server transaction log.

Additional Information:
– How to determine SQL Server database transaction log usage
– Virtual Log File Monitoring in SQL Server
– Check SQL Server Virtual Log Files Using PowerShell
– Determine Minimum Possible Size to Shrink the SQL Server Transaction Log File
– Using PowerShell To Record SQL Server Virtual Log File (VLF) Growth
– Performance Issue with large number of Virtual Log Files in SQL Server Transaction Log
– Remove Unnecessary SQL Server Transaction Log Files

DBCC OPENTRAN reports the open transactions in a SQL Server database.

DBCC SHOWCONTIG reports on SQL Server table fragmentation.