Master the diagnostic toolkit that every DBA needs to understand, maintain, and protect their SQL Server ecosystem.
Category: SQL Server DBALevel: Intermediate → AdvancedRead time: ~12 minUpdated: April 2026
DBCC (Database Console Commands) are a set of T-SQL statements that give DBAs direct access to SQL Server’s internal diagnostic and maintenance engine. Whether you’re troubleshooting corruption, reclaiming wasted space, or validating index integrity — these commands are the frontline of SQL Server health management.
In this article
- DBCC CHECKDB
- DBCC CHECKTABLE
- DBCC SHOWCONTIG
- DBCC INDEXDEFRAG
- DBCC SHRINKDATABASE
- DBCC SHRINKFILE
- DBCC SQLPERF
- DBCC DROPCLEANBUFFERS
- DBCC FREEPROCCACHE
- DBCC TRACEON / TRACEOFF
DBCC CHECKDB
The most important DBCC command in a DBA’s arsenal. CHECKDB validates the structural integrity of all objects in a database — checking allocation structures, system table integrity, and inter-object consistency. Every production SQL Server should run this on a regular schedule.
— Basic integrity check on a named database DBCC CHECKDB (‘YourDatabase’); — With NOINDEX: skips nonclustered indexes (faster, less I/O) DBCC CHECKDB (‘YourDatabase’) WITH NOINDEX; — With REPAIR_REBUILD: attempts safe, non-lossy repairs DBCC CHECKDB (‘YourDatabase’, REPAIR_REBUILD);
✓Best practice: Schedule CHECKDB weekly on OLTP databases and nightly on critical systems. Run against a restored backup copy to reduce I/O impact on production during off-peak hours.
DBCC CHECKTABLE
CHECKTABLE performs the same validation as CHECKDB but scoped to a single table or indexed view. Use this when you suspect corruption in a specific table or want a faster targeted check without the overhead of a full database scan.
— Check a specific table DBCC CHECKTABLE (‘dbo.Orders’); — Physical-only check — faster, detects torn pages and bad checksums DBCC CHECKTABLE (‘dbo.Orders’) WITH PHYSICAL_ONLY; — Suppress success messages for cleaner output in automation DBCC CHECKTABLE (‘dbo.Orders’) WITH NO_INFOMSGS;
✓Use PHYSICAL_ONLY in automated jobs for a faster, lightweight daily check. Reserve the full logical check for weekly maintenance windows.
DBCC SHOWCONTIG
SHOWCONTIG reports on fragmentation levels for a table’s indexes. It shows scan density, logical fragmentation percentage, and extent scan fragmentation — giving you the data to decide whether to rebuild or reorganize.
— Show fragmentation for all indexes on a table DBCC SHOWCONTIG (‘dbo.Orders’); — Target a specific index by ID DBCC SHOWCONTIG (‘dbo.Orders’, 1); — Scan all tables and indexes in the database DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
⚠Note: SHOWCONTIG is deprecated in newer SQL Server versions. For SQL Server 2005+, prefer sys.dm_db_index_physical_stats for fragmentation analysis — it provides richer, schema-aware output.
DBCC INDEXDEFRAG
INDEXDEFRAG performs online, interruptible defragmentation of a specified index. Unlike a full rebuild (which takes the index offline), INDEXDEFRAG is safe to run against production tables during business hours because it doesn’t hold locks for extended periods.
— Defragment a specific index DBCC INDEXDEFRAG (‘YourDatabase’, ‘dbo.Orders’, ‘IX_Orders_CustomerID’); — Using object and index IDs (from sys.objects / sys.indexes) DBCC INDEXDEFRAG (‘YourDatabase’, 1245247952, 2);
✓For fragmentation under 30%, use INDEXDEFRAG / ALTER INDEX REORGANIZE. For fragmentation over 30%, a full index rebuild (ALTER INDEX REBUILD) is more effective.
DBCC SHRINKDATABASE
SHRINKDATABASE reduces the overall size of all data and log files in a database by moving used pages to the front of each file and releasing unused space back to the OS. Useful after large bulk deletes, archiving operations, or after a database has been over-provisioned.
— Shrink to leave 10% free space in each file DBCC SHRINKDATABASE (‘YourDatabase’, 10); — Truncate only — release only contiguous free space from the end DBCC SHRINKDATABASE (‘YourDatabase’, TRUNCATEONLY); — Emulation run — no changes made, reports what WOULD happen DBCC SHRINKDATABASE (‘YourDatabase’) WITH NO_INFOMSGS;
⚠Use with caution: Shrinking databases causes significant index fragmentation. Always follow a shrink operation with an index rebuild pass. Never shrink as routine maintenance — only after a justified reduction in data volume.
DBCC SHRINKFILE
SHRINKFILE gives you precise control over individual data or log files, letting you target a specific file by name and set an exact target size in megabytes. This is the preferred approach over SHRINKDATABASE when you need surgical space management.
— Shrink a data file to 1024 MB DBCC SHRINKFILE (‘YourDatabase_Data’, 1024); — Shrink the transaction log file DBCC SHRINKFILE (‘YourDatabase_Log’, 256); — Truncate only — release pages from the end of the file DBCC SHRINKFILE (‘YourDatabase_Log’, TRUNCATEONLY);
✓Always run BACKUP LOG YourDatabase TO DISK = 'NUL' (in FULL recovery) or switch to SIMPLE recovery before shrinking log files to ensure committed transactions are cleared first.
DBCC SQLPERF
SQLPERF returns transaction log usage statistics for all databases on the instance. It shows log file size, space used, and the percentage of the log that’s currently utilized — critical for identifying log bloat and verifying that log backups are functioning correctly.
— View transaction log space usage for ALL databases DBCC SQLPERF (LOGSPACE); — Reset wait statistics counters (use carefully in production) DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
✓Alert on any database showing Log Space Used % consistently above 75% — this signals that log backups may be failing, VLF count is excessive, or a long-running transaction is holding the log open.
DBCC DROPCLEANBUFFERS
DROPCLEANBUFFERS flushes all clean data pages from the SQL Server buffer pool, forcing subsequent queries to read data from disk. This is used almost exclusively in performance testing and benchmarking to create a cold-cache baseline and ensure a fair comparison between query plans.
— Flush the buffer pool for a clean benchmark baseline CHECKPOINT; — First write dirty pages to disk DBCC DROPCLEANBUFFERS; — Optionally target a single database (SQL Server 2016+) DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
⚠Never run on production during business hours. Flushing the buffer pool forces all subsequent queries to read from storage, causing a severe — though temporary — performance degradation while the cache rewarms.
DBCC FREEPROCCACHE
FREEPROCCACHE clears all cached execution plans from the procedure cache, forcing SQL Server to recompile queries on their next execution. Used to test query plan recompilation, resolve parameter sniffing issues, or clear stale plans after a significant statistics update.
— Clear the entire plan cache (use sparingly in production) DBCC FREEPROCCACHE; — Remove a single plan by plan_handle (surgical approach) DBCC FREEPROCCACHE (0x060006001ECA230740215D1D000000000000000000000000); — Get a plan_handle for a specific query SELECT plan_handle, TEXT FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE TEXT LIKE ‘%YourQueryText%’;
✓Prefer removing individual plans by plan_handle in production. Clearing the entire cache causes widespread recompilation and a temporary CPU spike as all queries rebuild their plans simultaneously.
DBCC TRACEON / TRACEOFF
TRACEON and TRACEOFF enable and disable SQL Server trace flags — undocumented (and documented) flags that modify SQL Server’s behavior for debugging, enabling specific optimizations, or working around known issues. Common trace flags control cardinality estimator behavior, lock escalation, and parallel plan thresholds.
— Enable trace flag 1117 (equal autogrowth for all filegroup files) DBCC TRACEON (1117, -1); — -1 = applies globally — Enable TF 4199: all query optimizer hotfixes pre-2016 DBCC TRACEON (4199, -1); — Disable a trace flag DBCC TRACEOFF (4199, -1); — Check which trace flags are currently enabled DBCC TRACESTATUS (-1);
⚠Trace flags set with TRACEON are not persistent across SQL Server restarts. For permanent flags, add them as startup parameters via SQL Server Configuration Manager. Always test trace flags in a non-production environment first.
Understanding and regularly using these DBCC commands transforms reactive firefighting into proactive database management. A healthy SQL Server ecosystem isn’t achieved by accident — it’s the result of consistent, structured health checks, space management, and performance tuning built into your DBA runbook. Incorporate these commands into your monitoring scripts, maintenance plans, and incident response playbooks to stay ahead of corruption, fragmentation, and capacity issues before they impact your business.
The most resilient SQL Server environments are those where DBAs have moved beyond ad-hoc troubleshooting and built repeatable, automated processes around commands like these. Start with CHECKDB on a schedule, instrument your log space with SQLPERF, and build a fragmentation response policy around SHOWCONTIG results — and you’ll have a foundation that scales.
Need Expert SQL Server Support?
Falcon Source delivers enterprise-grade database consulting, managed DBA services, and SQL Server health assessments for organizations that can’t afford downtime.
Explore Our DBA Services Call us now at 972-515-2266 or email us at support@falconsource.com
#SQLServer #DBCC #DatabaseHealth #DBA #SQLPerformance #DatabaseAdministration #T-SQL #DataManagement