DBCC CHECKDB: Syntax, Errors & Repair Options

Our SQL Server support team runs DBCC CHECKDB as the first diagnostic step in every corruption case. This page documents the command: what it checks, the options that matter, how to read its output, and the correct order of repair decisions. It is written for the situation where CHECKDB has already reported errors and you need to decide what to do next.

What DBCC CHECKDB checks

DBCC CHECKDB validates the logical and physical integrity of all objects in a database. Per the official reference, one run performs the equivalent of three other commands plus additional work:

• DBCC CHECKALLOC — validates allocation structures (GAM, SGAM, PFS, IAM pages).
• DBCC CHECKTABLE — validates every table and indexed view: page linkage, index ordering, pointer consistency, correct offsets.
• DBCC CHECKCATALOG — validates catalog (system metadata) consistency.
• Contents of every indexed view.
• Link-level consistency between table metadata and FILESTREAM data, where FILESTREAM is used.
• Service Broker data validation.

Because CHECKALLOC, CHECKTABLE, and CHECKCATALOG are included, there is no reason to run them separately after a CHECKDB run. On SQL Server 2016 and later, CHECKDB does not validate persisted computed columns, UDT columns, or filtered indexes by default; the EXTENDED_LOGICAL_CHECKS option adds those checks at extra cost.

On databases with the default snapshot-based check, CHECKDB reads a transactionally consistent internal snapshot and does not block user activity. It is resource-intensive, not blocking.

Syntax and common options

DBCC CHECKDB
(
    [ database_name | database_id | 0 ]
    [ , NOINDEX
      | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
[ WITH { ALL_ERRORMSGS, NO_INFOMSGS, TABLOCK, ESTIMATEONLY,
         PHYSICAL_ONLY, DATA_PURITY, EXTENDED_LOGICAL_CHECKS,
         MAXDOP = n } ]

The invocation used in most scheduled jobs:

DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;

Options that come up in practice:

How often to run it, and the performance cost

The check is only useful if it runs before the last clean backup expires. The working rule: your CHECKDB interval must be shorter than your backup retention, because the standard recovery from corruption is restoring a backup taken before the corruption occurred. If you keep two weeks of backups, weekly CHECKDB is the minimum; nightly is common on smaller databases.

CHECKDB is CPU-, memory-, and I/O-heavy, and it uses tempdb for its internal worktables. Standard scheduling patterns, in order of preference:

1. Full DBCC CHECKDB WITH NO_INFOMSGS in the maintenance window, weekly or nightly.
2. Where the window is too short: PHYSICAL_ONLY nightly on production, plus a full logical check weekly, or a full check run against a restored copy of the backup on a secondary server. The restore-and-check pattern also verifies that the backup itself is restorable.
3. Enable the CHECKSUM page verification option on every database regardless. It detects I/O-path corruption at read time between CHECKDB runs and is the source of the 824 errors described below.

Note that running CHECKDB on an Always On readable secondary does not validate the primary's disk copy; each replica has its own files and each needs its own checks.

Reading the output

Two places matter: the SQL Server error log (823/824/825 are logged there when corruption is hit during normal operation) and the CHECKDB result set itself.

Errors 823, 824, 825

All three point at the storage path — disk, controller, driver, filter driver — rather than at SQL Server itself. Fixing the database without fixing the I/O subsystem produces a repeat incident. Pages that fail checksum are also recorded in msdb.dbo.suspect_pages, which is the input for single-page restores.

The minimum repair level line

A CHECKDB run that finds errors ends with a summary of this form:

CHECKDB found 0 allocation errors and 15 consistency errors in database 'YourDatabase'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (YourDatabase).

The minimum repair level line states the least aggressive repair option that would clear every reported error. It does not state that you should run repair; it states what repair would take. If the line says repair_rebuild, the damage is confined to structures that can be rebuilt from other data. If it says repair_allow_data_loss, at least one error can only be cleared by discarding data. Record the full error output before doing anything else — once the database is repaired or restored, the evidence is gone.

Repair options, in order of preference

1. Restore from backup — the correct answer

Restoring a clean backup is the primary method for recovering from corruption, and the Microsoft documentation says so directly. A restore returns the exact data that was there; repair does not promise that. With the database in FULL recovery and an unbroken log chain, a full restore plus log restores to the current point in time loses nothing. When only a few pages are damaged and they are known (from suspect_pages or the CHECKDB output), page-level restore repairs just those pages online in Enterprise edition, with the rest of the database available throughout.

2. REPAIR_REBUILD — safe, limited

Runs repairs that carry no possibility of data loss: quick fixes such as repairing missing rows in nonclustered indexes, and rebuilding damaged nonclustered indexes. If the minimum repair level is repair_rebuild, the equivalent manual fix is often simply rebuilding the named index, which does not require single-user mode. REPAIR_FAST exists for backward compatibility only and performs no repairs.

3. REPAIR_ALLOW_DATA_LOSS — what it actually does

This option clears errors by deallocating whatever it cannot fix. A damaged data page is deleted, along with every row on it; the structures that referenced the page are corrected to agree that the page is gone. The database becomes structurally consistent, and the rows are not recovered — they are removed. The option can also break foreign key relationships and transactional consistency, because it deletes rows without reference to constraints or to the transactions that wrote them. Microsoft's documentation recommends it only as a last resort when no backup exists, and recommends running DBCC CHECKCONSTRAINTS afterward to find the constraint violations it created.

Requirements and mechanics: the database must be in single-user mode (ALTER DATABASE ... SET SINGLE_USER); the repair runs inside a transaction, so it can be wrapped in an explicit BEGIN TRANSACTION and rolled back if the outcome is unacceptable; and CHECKDB must be run again afterward, because clearing one layer of errors can expose another.

When it is acceptable: no usable backup exists, or the business explicitly accepts losing the affected rows to regain a working database faster than a long restore would allow — with the decision recorded. When it is not acceptable: any time a clean backup with an intact log chain exists, or when nobody has identified which table and rows the damaged pages belong to. Deallocating pages from a ledger table is a different decision from deallocating pages from a staging table.

SQL Server suspect database: recovery steps

A database marked SUSPECT failed recovery — SQL Server could not roll the transaction log forward or back, usually because of corruption in the log or data files. The database is inaccessible in this state. The sequence, when no restorable backup exists:

-- 1. Confirm the state and read the error log for the root cause
SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabase';

-- 2. EMERGENCY mode: read-only access for admins, bypasses recovery
ALTER DATABASE YourDatabase SET EMERGENCY;

-- 3. Assess the damage
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 4. If proceeding with repair: single-user, then repair
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS);

-- 5. Verify, then return to service
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS;
ALTER DATABASE YourDatabase SET MULTI_USER;

Two facts about this sequence. First, EMERGENCY mode is also the window to extract data: tables can be read and copied out (BCP, SELECT INTO another database) before any repair is attempted, which is worth doing when the repair level is repair_allow_data_loss. Second, when the log file itself is the damaged component, running REPAIR_ALLOW_DATA_LOSS from EMERGENCY mode rebuilds the log — and a rebuilt log discards any transactions that were in flight, which breaks transactional consistency by definition. If the database is a replication publisher or part of an availability group, that has downstream consequences.

If the database shows IN RECOVERY or RECOVERY PENDING rather than SUSPECT, that is a different state with different handling — see SQL Server database in recovery.

What not to do

• Do not detach a suspect database. SQL Server refuses to attach a database that was detached while suspect, and the workarounds are worse than the problem. A detached corrupt database is strictly harder to recover than an attached one in EMERGENCY mode.
• Do not restart the instance hoping recovery succeeds this time. Recovery failed because of what is on disk; restarting reruns the same recovery against the same bytes. Repeated restarts add crash-recovery cycles on other databases and delay the actual fix.
• Do not run REPAIR_ALLOW_DATA_LOSS as the first step. It is the last step. Check backups first, extract data in EMERGENCY mode second.
• Do not delete or rebuild the transaction log manually because a forum post suggested it. Log rebuilds are the documented side effect of emergency-mode repair; doing it by other means produces the same data loss with fewer safeguards.
• Do not keep running on hardware that produced 823/824/825 errors without checking the storage stack. The database repair treats the symptom.

When to hand it over

Corruption cases are time-boxed by backup retention: every hour spent experimenting is an hour of backup history aging out. Our Microsoft SQL Server support team handles corruption and suspect-database recovery as a standard case type — backup-chain assessment, page-level restores, EMERGENCY-mode data extraction, and repair-option decisions with the data-loss trade-off stated before anything runs. A DBA is available on live chat 24/7.