The DBCC commands a DBA still uses on SQL Server 2016 to 2022, grouped by job, with the old ones that have gone or been replaced at the end. Examples tested on SQL Server 2017 and 2025.

Checking for corruption

DBCC CHECKDB
Checks the allocation, structure and logical integrity of every object in a database. It runs CHECKALLOC, CHECKTABLE and CHECKCATALOG for you. Run it regularly (weekly as a minimum) with NO_INFOMSGS.

DBCC CHECKTABLE
Checks one table or indexed view. Use WITH PHYSICAL_ONLY for a quicker check on large tables.

DBCC CHECKALLOC
Checks the disk space allocation structures of a database.

DBCC CHECKCATALOG
Checks the consistency of the system catalog.

DBCC CHECKFILEGROUP
Checks the tables and indexed views in one filegroup; useful for splitting CHECKDB on very large databases.

DBCC CHECKCONSTRAINTS
Finds rows that break a CHECK or FOREIGN KEY constraint, for example after constraints were disabled.

DBCC CHECKIDENT
Shows or resets the current identity value of a table.

Space and files

DBCC SHRINKFILE
Shrinks one data or log file. Use it for the log after a one-off growth; avoid shrinking data files routinely as it fragments indexes.

DBCC SHRINKDATABASE
Shrinks all files in a database. SHRINKFILE gives you more control and is usually the better choice.

DBCC SQLPERF (LOGSPACE)
Shows log size and percent used for every database. sys.dm_db_log_space_usage gives the same for the current database.

DBCC UPDATEUSAGE
Corrects page and row counts in the catalog. Rarely needed on SQL Server 2005 and later; mainly after upgrading an old database.

DBCC CLEANTABLE
Reclaims space after a variable-length column has been dropped.

Sessions and statistics

DBCC OPENTRAN
Shows the oldest active transaction in a database. A good first check when the log will not truncate.

DBCC INPUTBUFFER
Shows the last statement sent by a session. On 2016 SP1 and later sys.dm_exec_input_buffer does the same and can be joined to other DMVs.

DBCC SHOW_STATISTICS
Shows the header, density and histogram of a statistics object.

DBCC USEROPTIONS
Shows the SET options active for the current connection.

DBCC OUTPUTBUFFER
Shows the output buffer of a session in hex and ASCII.

DBCC HELP
Shows the syntax of a DBCC command, for example DBCC HELP (‘CHECKDB’).

Cache (not on a busy production server)

DBCC FREEPROCCACHE
Clears the plan cache, or one plan if you pass its plan_handle. ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears just one database (2016 and later).

DBCC DROPCLEANBUFFERS
Empties the buffer pool so you can test queries from a cold cache. Run CHECKPOINT first.

DBCC FREESYSTEMCACHE
Clears unused entries from all caches, or from one named cache.

Trace flags

DBCC TRACEON
Turns a trace flag on; add -1 for all sessions. For a permanent flag use a -T startup parameter instead.

DBCC TRACEOFF
Turns a trace flag off.

DBCC TRACESTATUS
Shows which trace flags are on; DBCC TRACESTATUS (-1) for global flags.

Examples

-- Integrity check of a database, errors only
DBCC CHECKDB (N'MyDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Log space used per database (or SELECT * FROM sys.dm_db_log_space_usage in the database)
DBCC SQLPERF (LOGSPACE);

-- Oldest open transaction in a database
DBCC OPENTRAN (N'MyDB');

-- Last statement sent by session 55 (or sys.dm_exec_input_buffer)
SELECT * FROM sys.dm_exec_input_buffer(55, NULL);

-- Statistics histogram for one index
DBCC SHOW_STATISTICS (N'dbo.MyTable', N'IX_MyTable_Col1') WITH HISTOGRAM;

-- Global trace flags that are on
DBCC TRACESTATUS (-1);

-- Index fragmentation (replaces DBCC SHOWCONTIG)
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild or reorganise (replaces DBCC DBREINDEX and DBCC INDEXDEFRAG)
-- ALTER INDEX ALL ON dbo.MyTable REBUILD WITH (ONLINE = ON);   -- ONLINE needs Enterprise
-- ALTER INDEX ALL ON dbo.MyTable REORGANIZE;

-- Clear the plan cache for one database only (instead of DBCC FREEPROCCACHE for the whole server)
-- ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Removed or replaced (do not use on 2016 to 2022)

DBCC DBREINDEX
Deprecated. Use ALTER INDEX … REBUILD.

DBCC INDEXDEFRAG
Deprecated. Use ALTER INDEX … REORGANIZE.

DBCC SHOWCONTIG
Deprecated. Use sys.dm_db_index_physical_stats.

DBCC PROCCACHE
Use sys.dm_os_memory_clerks and sys.dm_exec_cached_plans.

DBCC PINTABLE / UNPINTABLE
Do nothing since SQL Server 2005.

DBCC DBREPAIR, DBCC NEWALLOC, DBCC ROWLOCK
Removed. Use DROP DATABASE, DBCC CHECKALLOC and normal row locking instead.

DBCC dllname (FREE)
Deprecated, along with extended stored procedures in general.