SQL Server DBCC Commands Guide

calendar Jun 8, 2026 / · 10 min read · sql server dbcc commands database maintenance database administration maintenance scripts sql scripts consistency checks dbcc checkdb dbcc shrinkfile plan cache sys.dm_exec_requests sys.dm_db_log_space_usage sys.dm_exec_cached_plans sys.dm_exec_input_buffer sys.dm_exec_sql_text  ·
Share on: linkedin copy

An incident rarely announces which DBCC statement it needs. A log file stops truncating, a query slows after a plan changes, an identity column runs ahead of its table, and each one has a Database Console Command that answers it, sitting next to a few that can make the same situation worse. The DBCC statements are easiest to handle sorted by what they are used for, with a runnable statement for the ones you reach for most and a note on each one's cost and permissions.

Purpose and Overview

Where a dynamic management view reports state and leaves it alone, and ALTER INDEX or ALTER DATABASE change a single named object, the DBCC statements cover both jobs under one verb: some only read, some validate a whole database, and some rewrite storage or empty a memory cache. They fall into four categories, Maintenance, Miscellaneous, Informational, and Validation, but it is more useful to arrange them by the question a DBA is asking: is the database consistent, what is using the log, how do I free space or memory, and how do I fix a counter.

The practical split is between commands that observe and commands that change something. DBCC CHECKDB, CHECKTABLE, CHECKALLOC, and CHECKCATALOG validate structures. SQLPERF, OPENTRAN, SHOW_STATISTICS, and INPUTBUFFER report information. SHRINKDATABASE, SHRINKFILE, and the deprecated DBREINDEX and INDEXDEFRAG change how data sits on disk. FREEPROCCACHE, FREESYSTEMCACHE, and DROPCLEANBUFFERS empty memory structures. Several of these also have newer DMV or ALTER equivalents, and the sections below point to them where one exists.

Permissions differ by command. CHECKDB, CHECKALLOC, and the shrink commands require membership in the sysadmin fixed server role or the db_owner fixed database role. CHECKTABLE and DBREINDEX also allow table owners and db_ddladmin. The cache commands FREEPROCCACHE and FREESYSTEMCACHE require ALTER SERVER STATE. SQLPERF (LOGSPACE) requires VIEW SERVER STATE. Check the permission before you build an unattended job around any of them.

Code Breakdown

The table groups the commands you will actually run. Treat the validation rows as routine, the informational rows as on-demand, and the maintenance and cache rows as deliberate actions that need a reason.

FamilyCommandWhat it does
ValidationDBCC CHECKDBChecks the logical and physical integrity of all objects in the database; runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG internally
ValidationDBCC CHECKTABLEChecks the integrity of all pages and structures of one table or indexed view
ValidationDBCC CHECKALLOCChecks the consistency of disk space allocation structures for a database
ValidationDBCC CHECKCATALOGChecks for catalog consistency within a database
MaintenanceDBCC SHRINKDATABASEShrinks the data and log files of a database
MaintenanceDBCC SHRINKFILEShrinks one data or log file; can empty a file with EMPTYFILE
MaintenanceDBCC DBREINDEXRebuilds indexes; feature will be removed in a future version, use ALTER INDEX
MaintenanceDBCC INDEXDEFRAGDefragments indexes; feature will be removed in a future version, use ALTER INDEX
InformationalDBCC SQLPERFProvides transaction log space usage statistics for all databases
InformationalDBCC OPENTRANDisplays the oldest active transaction in a database's log
InformationalDBCC SHOW_STATISTICSDisplays the header, histogram, and density vector of a statistics object
InformationalDBCC INPUTBUFFERDisplays the last statement sent from a client to the instance
CacheDBCC FREEPROCCACHERemoves all plans, or one plan, or one resource pool's entries from the plan cache
CacheDBCC FREESYSTEMCACHEReleases unused cache entries from all caches or from one Resource Governor pool cache
CacheDBCC DROPCLEANBUFFERSRemoves all clean buffers from the buffer pool

Validation: DBCC CHECKDB

CHECKDB is the integrity check you schedule and do not skip. Because it already runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG, plus checks of indexed views, FILESTREAM links, and Service Broker data, those commands do not need to be run separately. NO_INFOMSGS suppresses informational messages so that any output line is an error worth reading.

1DBCC CHECKDB (N'YourDatabase') WITH NO_INFOMSGS;

Two options control cost. PHYSICAL_ONLY limits the check to the physical structure of page and record headers and allocation consistency, and it is the form recommended for frequent use on production systems, with a full run still advised periodically. ESTIMATEONLY displays the estimated amount of tempdb space the check needs without running it. Run the estimate first on a large database. REPAIR_ALLOW_DATA_LOSS is an emergency last resort; restoring from the last known good backup is always recommended instead.

While CHECKDB runs, percent_complete in sys.dm_exec_requests reports its progress, as it does for CHECKTABLE, CHECKFILEGROUP, and the shrink commands:

1SELECT session_id, command, percent_complete
2FROM   sys.dm_exec_requests
3WHERE  percent_complete > 0;

Informational: DBCC SQLPERF and the log

DBCC SQLPERF (LOGSPACE) returns the current log size and percentage of log space used for each database, in one statement.

1DBCC SQLPERF (LOGSPACE);

From SQL Server 2012, use the sys.dm_db_log_space_usage DMV instead, which returns space usage information for the transaction log, including used_log_space_in_percent:

1SELECT * FROM sys.dm_db_log_space_usage;

Informational: DBCC OPENTRAN

An open transaction can prevent the log from truncating completely, because only the inactive part of the log can be truncated. OPENTRAN shows the oldest active transaction, including its SPID and start time. WITH TABLERESULTS returns the output in a form you can insert into a table for comparison between runs.

1DBCC OPENTRAN (N'YourDatabase');

Informational: DBCC INPUTBUFFER

INPUTBUFFER shows the last statement a session sent. Starting with SQL Server 2014 SP2, sys.dm_exec_input_buffer, which takes a session ID and request ID, returns the same information. Without VIEW SERVER STATE or sysadmin membership, DBCC INPUTBUFFER shows only your own session.

1SELECT * FROM sys.dm_exec_input_buffer(52, NULL);

Cache: removing one plan instead of all

DBCC FREEPROCCACHE with no argument evicts every plan, and incoming queries then compile new plans, which can cause a sudden, temporary decrease in query performance. The argument form removes a single plan by plan_handle. Find the handle in sys.dm_exec_cached_plans with sys.dm_exec_sql_text:

1SELECT plan_handle, st.text
2FROM   sys.dm_exec_cached_plans
3CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
4WHERE  st.text LIKE N'%YourQueryFragment%';
5
6DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

Replace the sample handle with the value returned. ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears the plan cache for the current database, available from SQL Server 2016.

Cache: DBCC DROPCLEANBUFFERS

DBCC DROPCLEANBUFFERS lets you test queries with a cold buffer cache without shutting down and restarting the server. Run CHECKPOINT first so dirty pages are written to disk and the buffers become clean.

1CHECKPOINT;
2DBCC DROPCLEANBUFFERS;

Identity and statistics

DBCC CHECKIDENT (table_name, NORESEED) returns the current identity value and the current maximum value in the column without changing anything. RESEED, new_reseed_value sets the current identity value; setting it below the column's maximum on a non-empty table produces error 2627 on later inserts when a primary key or unique constraint exists, or duplicate values when one does not. DBCC SHOW_STATISTICS displays the header, histogram, and density vector the optimizer uses for cardinality estimates, and it requires SELECT permission on the table.

Key Benefits and Use Cases

  • Corruption detection: the validation commands check page, allocation, and catalog structures and report errors.
  • Log troubleshooting: SQLPERF shows how full each log is, and OPENTRAN points at the transaction that can block truncation.
  • Targeted plan eviction: one plan_handle can be removed instead of the whole cache, which limits the recompile cost.
  • Identity repair: CHECKIDENT compares the identity value with the column maximum and reseeds when they disagree.
  • Optimizer insight: SHOW_STATISTICS exposes the header, histogram, and density vector behind a cardinality estimate.

Performance Considerations

  • CHECKDB uses a snapshot: it creates an internal database snapshot, as a set of sparse files in the same folder as the database files, to get a transactionally consistent view without blocking. The snapshot files grow with the amount of data updated in the source database during the run, so keep free space on that volume.
  • No snapshot means locks: if a snapshot cannot be created, or TABLOCK is specified, CHECKDB acquires locks instead. Against tempdb it never uses a snapshot and takes shared table locks.
  • Size tempdb: use ESTIMATEONLY to see the tempdb space required before scheduling a check on a large database.
  • Shrinking fragments indexes: a shrink operation does not preserve the fragmentation state of indexes and can increase index fragmentation, which might reduce read I/O throughput for queries using large scans.
  • Shrink is not maintenance: shrink operations are not regular maintenance, and AUTO_SHRINK should not be set to ON without a specific requirement.
  • Cache clears cost CPU: after a global FREEPROCCACHE, every incoming query compiles a new plan. Several sp_configure changes, including max degree of parallelism and max server memory, also clear the procedure cache when reconfigured.
  • DBREINDEX is offline: unlike ALTER INDEX with REORGANIZE, it holds a shared table lock for nonclustered indexes and an exclusive lock for a clustered index.

Practical Tips

  • Schedule validation through SQL Agent: run CHECKDB on a schedule, with PHYSICAL_ONLY for frequent runs and a full check periodically. The open-source maintenance solution listed in References wraps integrity checks and index and statistics maintenance.
  • Look at what the error log says: when CHECKDB finishes, a message is written to the SQL Server error log stating success and run time, or that the command was terminated with a state value.
  • Use the narrowest form: evict one plan, shrink one file with TRUNCATEONLY (which releases free space at the end of the file without moving pages), or reseed one table, rather than the database-wide forms.
  • Keep shrink out of maintenance plans: data and log files that grow from regular, recurring business operations do not need shrink operations, and a shrink is most effective after an operation that creates unused space, such as a truncate table or drop table.
  • Migrate legacy index jobs: replace DBCC DBREINDEX and DBCC INDEXDEFRAG with ALTER INDEX REBUILD or REORGANIZE, the supported replacement.
  • Test destructive commands off production: DROPCLEANBUFFERS and a global FREEPROCCACHE change performance for every session, so try them on a test instance first.

Conclusion

DBCC is a mixed set of statements: validation you schedule, diagnostics you run during an incident, and storage and cache commands that carry a cost. Knowing which group a command belongs to, and its permissions and side effects, is the useful part. Use this page as the index and follow the deep dives below for the commands you run most.

Deep dives in this series:

References

Posts in this series