DBCC stands for database console commands, which are commands executed on a specific database. The DBCC command set allows you to perform various administrative operations or queries on databases through scripting, instead of using the graphical interface of Management Studio. To execute these commands, you typically need a high-level role (such as sysadmin, serveradmin, db_owner, etc.).

The commands are divided into several groups:

• Maintenance – a group of commands that perform maintenance operations on tables, indexes, or files.
• Informational – running these commands displays information about a specific database.
• Validation – commands that verify the consistency of objects.
• Others – commands for enabling or disabling certain functionality.

In general, it is not recommended to execute most of these commands unless you have a strong knowledge of SQL Server administration. Below are examples of interesting commands, with commands that may have a temporary dramatic impact on performance highlighted in red.

Maintenance Using DBCC Commands

DBCC DBREINDEX – rebuilds an index for a specific table.

DBCC DROPCLEANBUFFERS – if a query is run repeatedly, SQL Server caches the result in the buffer pool to retrieve it faster. This command is used to clear the buffer pool for testing queries with a clean buffer pool.

(!) DBCC FREEPROCACHE – Clears the cache and execution plans. It can lead to a significant slowdown in regularly executed queries, as recreating plans takes some time. This DBCC command is sometimes used when you intentionally want to clear plans, for example, because significant changes have been made to the database that make old execution plans inefficient, and you want to reload them.

(!) DBCC SHRINKDATABASE – Shrinks data (.mdf and .ndf) and log (.ldf) files. It is used when there is a lot of unallocated space in the database. For example, if the database is 100 GB and has 50 GB of unallocated space, running the shrink operation will reduce the size of the data files to 50 GB. Be aware that the shrink operation takes a long time. This command does not delete data from the database; it only frees up space from the unused portion of the database file. After performing a shrink operation, index fragmentation increases dramatically, so it is necessary to maintain indexes (REBUILD or REORGANIZE). During the shrink operation, exclusive locks are placed on objects, reducing the database’s usability. Therefore, it is advisable to use shrink sparingly and preferably during non-business hours.

(!) DBCC SHRINKFILE – works similarly to SHRINKDATABASE but shrinks only one file, whereas SHRINKDATABASE works on all files associated with a database.

Example of SHRINKDATABASE

Let’s take the freely available Microsoft sample database AdventureworksDW2016CTP3, which has two files:

• Primary: AdventureworksDW2016CTP3_Data.mdf (5334 MB)
• Log: AdventureworksDW2016CTP3_Log.ldf (147 MB)

Finding the List of Objects in the SQL Database

Using a script you can view a list of objects and their sizes, then delete one of them. I’ve decided to delete the FactResellerSalesXL_PageCompressed object, freeing up 2508 MB.

Checking the Current Size of the SQL Database Using sp_spaceused

Using the database properties or the system procedure sp_spaceused to check the current size of the database, you can see that the data file size remains unchanged, but the unused space has increased by the capacity of the deleted object from the previous step.

Cleaning Unused Space in the Database Using SHRINKDATABASE

To physically release this unused space, you must perform a SHRINKDATABASE operation. Be prepared for a longer execution time. The operation took 13 minutes for a database larger than 5GB.

Informational DBCC Commands

Informational commands are non-risky; they do not perform any actions and only display information:

• DBCC INPUTBUFFER – a useful command that displays the text of the last executed SQL command in a specific session. (The syntax is DBCC INPUTBUFFER (session_number))
• DBCC OPENTRAN – displays information about recently opened transactions.
• DBCC SHOW_STATISTICS – displays a list of statistics for a specific table.
• DBCC SHOWCONTIG – provides information about the fragmentation of a specific object.

Validation DBCC – Check Scripts

In this group of commands, you’ll find those that perform check operations (prefixed with CHECK). I’ll mention only one of the most important commands.

DBCC CHECKDB – Database Consistency Check

Every database admin should run this periodically. It checks the consistency of the database (both physical and logical consistency) and verifies all objects in the specified database.

Other Validation DBCC Commands

The validation category includes others (CHECKALLOC, CHECKCATALOG, CHECKCONSTRAINTS, etc.), but the CHECKDB command covers all of them, so I won’t list them here.

For more information about DBCC commands, you can refer to Microsoft’s documentation under the About DBCC and related documentation.