SQL Server DBCC CHECKDB Guide and Repair Options

calendar Jun 7, 2026 / · 10 min read · sql server database corruption consistency checks database maintenance dbcc commands database administration maintenance scripts sql scripts dbcc checkdb sys.databases  ·
Share on: linkedin copy

Running DBCC CHECKDB on a schedule is only the start, because its options change what it checks, how long it takes and what it can do to your data. This post covers the check options, a script that runs the command against every online user database, and the three repair levels, including why REPAIR_ALLOW_DATA_LOSS is an emergency last resort.

Purpose and Overview

Beginning with SQL Server 2014, DBCC CHECKDB builds its internal snapshot from physical files. For a database with files my_DB.mdf and my_DB.ndf, the snapshot uses my_DB.mdf_MSSQL_DBCC11 and my_DB.ndf_MSSQL_DBCC11, created in the same folder as the originals, marked as sparse at the file system level, and deleted when the check finishes. Before SQL Server 2014 the snapshot used named file streams, which ordinary file utilities cannot see. The version detail matters because the snapshot's disk space comes from the volume that holds your data files, and an unexpected operating-system shutdown mid-check can leave the files behind.

That snapshot is what lets the check run without blocking: DBCC CHECKDB "uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems." Inside it, the command runs DBCC CHECKALLOC on the database, runs DBCC CHECKTABLE on every table and view, runs DBCC CHECKCATALOG, validates the contents of every indexed view, validates link-level consistency for FILESTREAM data, and validates the Service Broker data. None of those three sub-commands needs to be run separately.

The result is a list of errors, and at the end of that list the command names the repair option it would need. Treat that as information. If errors are reported, restore the database from backup instead of running DBCC CHECKDB with one of the REPAIR_* options. Repair applies when no backup exists, and the order is: detect early, restore first, repair last.

The command also leaves a record. SQL Server writes a message to the error log when the command finishes, and it records the date of the last clean check. When a database starts, that date is written to the error log in the form "CHECKDB for database '' finished without errors on ...". That message is how you read the last known clean check from the log.

Code Breakdown

The script loops through every online user database and runs a full check on each, with informational messages suppressed, so that a clean run returns only the success message and any error text stands out. The script also prints one progress line per database, which marks where each check starts. The system-database filter is explicit in the query.

 1SET NOCOUNT ON;
 2
 3DECLARE @db  sysname,
 4        @sql nvarchar(max);
 5
 6DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
 7    SELECT name
 8    FROM   sys.databases
 9    WHERE  state_desc = N'ONLINE'
10      AND  name NOT IN (N'master', N'model', N'msdb', N'tempdb')
11    ORDER BY name;
12
13OPEN db_cursor;
14FETCH NEXT FROM db_cursor INTO @db;
15
16WHILE @@FETCH_STATUS = 0
17BEGIN
18    SET @sql = N'DBCC CHECKDB (' + QUOTENAME(@db) + N') WITH NO_INFOMSGS;';
19    PRINT N'--- Checking ' + @db + N' at ' + CONVERT(varchar(30), SYSDATETIME(), 121);
20    EXEC sys.sp_executesql @sql;
21
22    FETCH NEXT FROM db_cursor INTO @db;
23END
24
25CLOSE db_cursor;
26DEALLOCATE db_cursor;

The database cursor

The cursor reads sys.databases and keeps rows whose state_desc is ONLINE. state_desc is the description of the database state, with values including ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY and OFFLINE, so the filter limits the loop to databases that are available. The NOT IN list skips the four system databases by name. Note that DBCC CHECKDB against master also runs a second check internally on the Resource database, which is why it can return extra results; against tempdb it does no allocation or catalog checks and takes shared table locks, because snapshots are not available there. Include the system databases in a separate, deliberate run rather than by deleting the filter.

QUOTENAME(@db) wraps each name in brackets before it is concatenated into dynamic SQL, so a database name with spaces or special characters does not break the statement.

Permissions

DBCC CHECKDB requires "membership in the sysadmin fixed server role or the db_owner fixed database role." A scheduled job that runs under a lower-privileged account will fail on databases it does not own.

NO_INFOMSGS

NO_INFOMSGS suppresses all informational messages. With it, a clean database returns only the message "The command(s) completed successfully." instead of the per-object detail, so any other line in the output, apart from the script's own progress lines, is worth reading and is simple to log and alert on. ALL_ERRORMSGS is not needed in this script. That option "displays all reported errors per object. All error messages are displayed by default. Specifying or omitting this option has no effect," so the script omits it.

PHYSICAL_ONLY for large databases

PHYSICAL_ONLY limits the check to "the integrity of the physical structure of the page and record headers and the allocation consistency of the database." It can still detect torn pages, checksum failures and common hardware failures, and it can give a much shorter run-time on large databases. It suits frequent use on production systems alongside a periodic full run. Three limits to know: it always implies NO_INFOMSGS, it is not allowed with any repair option, and it skips all checks of FILESTREAM data and the column-value checks.

EXTENDED_LOGICAL_CHECKS

Under compatibility level 100 or higher, EXTENDED_LOGICAL_CHECKS performs logical consistency checks on indexed views, XML indexes and spatial indexes, where present. It does so by building an internal query that intersects the internal index table with the user table, which "can have a significant effect on performance, and its progress can't be tracked." Use it only if you suspect index issues unrelated to physical corruption, or if page-level checksums are off and you suspect column-level hardware corruption. From SQL Server 2016, expression evaluation for persisted computed columns, user-defined type columns and filtered indexes is also skipped by default and runs only with this option.

Other options worth knowing

  • NOINDEX skips the intensive checks of nonclustered indexes on user tables; system table indexes are always checked.
  • ESTIMATEONLY displays the estimated tempdb space the check would need, and does not run the check.
  • TABLOCK takes locks instead of using the snapshot, which runs faster on a busy database but lowers concurrency, and skips DBCC CHECKCATALOG and Service Broker validation.
  • MAXDOP overrides the instance setting for the statement, and applies to SQL Server 2014 Service Pack 2 and later.
  • DATA_PURITY checks for column values that are not valid or are out of range. Column-value integrity checks are enabled by default. For databases upgraded from earlier versions, they are not enabled until DBCC CHECKDB WITH DATA_PURITY has run error-free once.

Repair options

Repair needs the database in single-user mode, for all three options. The sequence below wraps the repair in a transaction, which suits the REPAIR_* options because the result can be accepted or rolled back:

 1ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 2
 3BEGIN TRANSACTION;
 4    DBCC CHECKDB (N'YourDatabase', REPAIR_REBUILD);   -- or REPAIR_ALLOW_DATA_LOSS as a last resort
 5-- Inspect the output, then run ONE of these to end the transaction:
 6-- COMMIT TRANSACTION;
 7-- ROLLBACK TRANSACTION;
 8
 9-- Only after the transaction has ended (ALTER DATABASE is not allowed inside a transaction):
10ALTER DATABASE YourDatabase SET MULTI_USER;

The three levels behave differently:

  • REPAIR_REBUILD performs "repairs that have no possibility of data loss." That covers quick repairs such as missing rows in nonclustered indexes and slower ones such as rebuilding an index. It does not repair errors involving FILESTREAM data.
  • REPAIR_ALLOW_DATA_LOSS "tries to repair all reported errors," and those repairs "can cause some data loss." Some repairs deallocate a row, a page or a series of pages, and the deallocated data is "no longer accessible or recoverable." Foreign key constraints are not checked or maintained during the repair, so referential integrity may be wrong afterward; run DBCC CHECKCONSTRAINTS to find the violations.
  • REPAIR_FAST "maintains syntax for backward compatibility only. No repair actions are performed."

Before any repair, make physical copies of every file in the database (.mdf, .ndf, .ldf and the other containers). Consider EMERGENCY mode and extracting what you can from critical tables first. In emergency mode, REPAIR_ALLOW_DATA_LOSS can rebuild a damaged transaction log, and after that rebuild "there's no full ACID guarantee." You cannot run DBCC CHECKDB in emergency mode inside a user transaction and roll that transaction back afterward.

Key Benefits and Use Cases

  • Finding damage while a good backup still exists — a regular check surfaces errors early, which keeps the restore path open.
  • Instance-wide coverage in one job — the cursor loop checks every online user database and writes a single log of problems.
  • Fast or full modes — PHYSICAL_ONLY fits large databases into short windows, while the default run covers the logical checks.
  • No blocking by default — the internal snapshot lets the check run while the database is in use.
  • Alertable output — with NO_INFOMSGS, a clean database returns only the success message, so anything else is an error to investigate.
  • Estimating before running — ESTIMATEONLY gives the tempdb space the check needs so you can size it first.

Performance Considerations

  • tempdb space: ESTIMATEONLY exists because the check needs tempdb space. Run it first on a large database and compare the estimate with free space in tempdb.
  • Snapshot disk space: the sparse snapshot files live in the same folder as the data files and grow with the amount of data changed in the source database while the check runs. Their size on disk "increases based on how much data is updated in the source database."
  • Full runs are slow on large databases: A full run "might take considerably longer to complete than earlier versions" because the logical checks are broader and some of the underlying structures are more complex. That run-time is the reason to use PHYSICAL_ONLY for the frequent checks.
  • Disabled indexes and memory-optimized tables: DBCC CHECKDB does not examine disabled indexes. For memory-optimized tables, validation occurs only on disk-based tables; the files in memory-optimized filegroups get a CHECKSUM validation during backup and recovery.
  • TABLOCK trade-off: it speeds up a check on a loaded database at the cost of concurrency and of the catalog and Service Broker checks.
  • Snapshot failure: if a snapshot cannot be created, the command falls back to locks, which requires an exclusive database lock for the allocation checks and shared table locks for the table checks.

Practical Tips

  • Schedule the full check and a PHYSICAL_ONLY check. Use PHYSICAL_ONLY for frequent runs on production systems and a periodic run with no options. How often depends on the business, so set the interval from your recovery requirements.
  • Alert on unexpected output. Log the job output to a table or file and notify when it holds anything besides the progress lines and the success message.
  • Read the error log after a failure. If the command stops before completing, the log message carries a state value, for example state 1 for error 8967 (an internal DBCC error) and state 4 for an assert or access violation.
  • Look for stack dumps. A stack dump is created in the SQL Server LOG directory whenever the command detects a corruption error.
  • Restore before repairing. The order is restore from the last known good backup, and use REPAIR_ALLOW_DATA_LOSS only when restoring is not possible.
  • After a repair, run DBCC CHECKCONSTRAINTS and back up. Both are advised, as is re-validating data in replicated databases, where repairs might not be replicated.
  • Use a maintenance solution for logging. The DatabaseIntegrityCheck procedure in an open-source maintenance solution checks the integrity of databases and can log each command to a table.

Conclusion

DBCC CHECKDB is the integrity check SQL Server provides, and its value depends on running it early and acting on the result in the right order: restore from a good backup first, repair only when no backup exists. Know the options before you need them, particularly PHYSICAL_ONLY for large databases and the data-loss behavior of REPAIR_ALLOW_DATA_LOSS. For the rest of the DBCC family, see the complete DBCC command reference.

References

Posts in this series