SQL Server DBCC CHECKDB: Complete Guide and Repair Options

Corruption rarely announces itself. A bit flips on a SAN, a controller loses power mid-write, a driver mishandles a flush — and the damaged page sits unread for weeks until a query finally touches it and the database throws error 824. By then the corruption may already be inside every backup you keep. DBCC CHECKDB is the one statement that finds the damage early, while you still have a clean restore point, and this guide covers how to run it and what its repair options actually do.

Purpose and Overview

A database can be corrupt for a long time before anyone notices. The pages most likely to rot are the ones read least often — an archive table, a rarely-queried index — and SQL Server only validates a page when it reads it. So the failure mode is brutal: corruption accumulates silently across nightly full backups, and the first symptom is a production read failure on data that no longer has a clean copy anywhere. The defense is proactive validation, not reactive firefighting.

DBCC CHECKDB performs the most thorough integrity check SQL Server offers. It runs CHECKALLOC to validate disk-space allocation structures, CHECKTABLE against every table and indexed view to verify page linkages and row consistency, and CHECKCATALOG to confirm the system metadata agrees with itself. It also validates Service Broker data and, with the right options, the logical relationships between a clustered index and its non-clustered indexes. In one statement it answers the only question that matters before a disaster: is this database physically and logically sound?

The command reports errors as numbered messages with a severity and a suggested minimum repair level. That suggested level is information, not instruction — REPAIR_ALLOW_DATA_LOSS appearing in the output does not mean you should run it. The correct first response to corruption is almost always to restore from the last known-good backup, and repair is the path of last resort when no such backup exists.

Code Breakdown

The script below loops every online database on the instance and runs a full check, surfacing only real problems. Schedule it and treat any output as an incident.

 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  database_id > 4          -- skip system DBs, or remove to include them
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)
19             + N') WITH NO_INFOMSGS, ALL_ERRORMSGS;';
20    PRINT N'--- Checking ' + @db + N' at ' + CONVERT(varchar(30), SYSDATETIME(), 121);
21    EXEC sys.sp_executesql @sql;
22
23    FETCH NEXT FROM db_cursor INTO @db;
24END
25
26CLOSE db_cursor;
27DEALLOCATE db_cursor;

The database cursor

The cursor reads sys.databases and filters on state_desc = 'ONLINE' because CHECKDB cannot run against a database that is offline, restoring, or recovering — attempting it just raises an error. Filtering database_id > 4 skips master, model, msdb, and tempdb; remove that predicate (and check msdb especially) once you have validated it works.

NO_INFOMSGS and ALL_ERRORMSGS

NO_INFOMSGS suppresses the long list of informational "rows processed" messages so the only output is problems. ALL_ERRORMSGS removes the historical 200-error display cap, ensuring a badly corrupted database reports every error rather than truncating. Together they make the output trivially easy to alert on: empty means clean.

PHYSICAL_ONLY for large databases

On a multi-terabyte database a full CHECKDB may not fit your maintenance window. WITH PHYSICAL_ONLY skips the expensive logical checks and validates page integrity and allocation only, running far faster while still catching the most common storage-layer corruption. Run the full check less frequently and PHYSICAL_ONLY in between.

EXTENDED_LOGICAL_CHECKS

At the other extreme, WITH EXTENDED_LOGICAL_CHECKS adds validation of indexed views, XML indexes, and spatial indexes against their base data. It is thorough and slow — reserve it for targeted investigation, not routine runs.

Repair Options

When corruption is found and no clean backup exists, CHECKDB offers two repair levels, and the database must be in SINGLE_USER mode for either:

1ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
2
3-- Non-destructive: rebuilds rows and indexes where it safely can
4DBCC CHECKDB (N'YourDatabase', REPAIR_REBUILD) WITH NO_INFOMSGS;
5
6-- LAST RESORT: deallocates corrupt pages, destroying the data on them
7DBCC CHECKDB (N'YourDatabase', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
8
9ALTER DATABASE YourDatabase SET MULTI_USER;

REPAIR_REBUILD fixes problems without losing data, but only a narrow class of errors qualify. REPAIR_ALLOW_DATA_LOSS fixes everything by deallocating the damaged pages — which means deleting whatever rows lived there and breaking any foreign keys that referenced them. It returns the database to a structurally consistent state at the cost of data, and it is never the right first move when a restore is possible.

Key Benefits and Use Cases

• Early corruption detection — finds torn pages and allocation errors weeks before a query stumbles on them in production.
• Backup validation — running CHECKDB on a restored copy proves both the backup and the data are sound.
• Full or fast modes — PHYSICAL_ONLY fits large databases into tight windows; the full check covers logical integrity.
• Non-blocking operation — the internal snapshot lets the check run against a live database without blocking writers.
• Scriptable across the instance — one cursor validates every database and feeds a single alert.
• Clear, alertable output — with NO_INFOMSGS, any output line is a genuine problem.

Performance Considerations

• tempdb pressure: CHECKDB builds internal worktables in tempdb; undersized tempdb slows the check dramatically.
• Snapshot disk space: the transaction-consistent snapshot lives on the same volume as the data files and needs room for the copy-on-write activity.
• I/O load: the check reads every allocated page, so on production it competes hard with the workload — schedule off-peak.
• PHYSICAL_ONLY tradeoff: faster and lighter, but it skips logical checks, so it will miss some corruption the full check catches.
• Offload when possible: run the heavy check on a restored copy or a readable secondary to spare the primary.

Practical Tips

• Schedule it weekly at minimum — wire it into SQL Agent or an open-source maintenance solution so it never depends on someone remembering.
• Alert on any non-empty result — log the output to a table and page on the first error; corruption should find you, not the other way around.
• Never lead with REPAIR_ALLOW_DATA_LOSS — restore from the last clean backup first; repair only when no backup can help.
• Validate backups by restoring them — a backup you have never restored and checked is a hope, not a recovery plan.
• Investigate the root cause — corruption is usually a storage or driver fault; fixing the database without fixing the hardware just buys time.

Conclusion

DBCC CHECKDB is the proactive defense that turns corruption from a catastrophe into a caught-early annoyance. Run it on a schedule, alert on its output, and keep clean backups so that repair is a tool you study but rarely use. For the full DBCC toolkit, see the complete DBCC command reference.

References

• DBCC CHECKDB (Transact-SQL) — the official reference for every CHECKDB option and repair level.
• SQL Server Maintenance Solution — the DatabaseIntegrityCheck procedure that runs CHECKDB across an instance with logging.
• sqlserver-kit — community scripts and checklists covering corruption handling and integrity checks.