DBCC CHECKDB has no true replacement but you can lower its impact and choose safer repair paths. Physical only checks, a controlled TempDB and a snapshot reduce the impact on a busy server. Restoring from a good backup beats every repair option. Page restore fixes a few damaged pages without a full restore. Repair options come last because they can delete data.
DBCC CHECKDB slows your server and you search for an alternative. Better questions ask how to check less aggressively and what to do when the check finds damage.
This guide shows the documented ways to reduce the impact of DBCC CHECKDB and the safer options to repair corruption. All commands are untested and follow the official page.
What does DBCC CHECKDB Do?
Microsoft states that DBCC CHECKDB checks the logical and physical integrity of every object in the database. It executes DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG and validates indexed views and Service Broker data. See DBCC CHECKDB.
Microsoft states that the command uses an internal database snapshot, which prevents blocking and concurrency problems. SQL Server locks the database only when it cannot create the snapshot or when you specify TABLOCK.
How do You Reduce the Impact of DBCC CHECKDB?
| Option | What Microsoft States | Trade Off |
|---|---|---|
| PHYSICAL_ONLY | Small overhead check for torn pages, checksum failures and common hardware faults | Omits column value checks and FILESTREAM checks |
| ESTIMATEONLY | Shows the TempDB space that the check needs | No check happens |
| MAXDOP | Overrides the parallelism setting for the statement | Resource Governor can cap it |
| TABLOCK | Faster on a loaded database | Lower concurrency and fewer checks |
DBCC CHECKDB (CheckDemoDB) WITH PHYSICAL_ONLY
DBCC CHECKDB (CheckDemoDB) WITH ESTIMATEONLY
DBCC CHECKDB (CheckDemoDB) WITH MAXDOP = 4Microsoft recommends PHYSICAL_ONLY for frequent use on production systems and a full check at longer intervals. TABLOCK omits DBCC CHECKCATALOG and Service Broker validation.
Why is a Backup the Best Alternative?
Microsoft always recommends a restore from the last known good backup as the primary method to recover from errors that DBCC CHECKDB reports.
Restore from the backup instead of using a REPAIR option whenever a good backup exists. Test your backups on a schedule so the restore works when you need it.
When does Page Restore Replace a Full Repair?
Page restore replaces only damaged pages from a backup and the rest of the database remains available. It needs the full or bulk logged recovery model. See restore pages and our guide to SQL Server page level corruption.
What are the DBCC CHECKDB Repair Options?
| Option | What Microsoft States |
|---|---|
| REPAIR_REBUILD | Repairs with no possibility of data loss. Quick repairs and index rebuilds. |
| REPAIR_ALLOW_DATA_LOSS | Tries to repair all errors and can lose data. Emergency last resort. |
| REPAIR_FAST | Kept for backward compatibility. No repair happens. |
- Copy the database files first.
- Execute DBCC CHECKDB without a repair option to find the repair level that you need.
- Set the database to single user mode.
- Execute the repair inside a transaction so you can review the result.
- Back up the database after the repair.
Microsoft adds that foreign key constraints are not checked during a data loss repair. Execute DBCC CHECKCONSTRAINTS afterward. For a real error case see how to fix SQL Server error 5242.
Frequently Asked Questions
Is There a Real Alternative to DBCC CHECKDB?
No. Other tools reduce its impact or repair damage. Microsoft documents DBCC CHECKDB as the integrity check.
Does DBCC CHECKDB Lock My Database?
Not by default. Microsoft states that it uses an internal snapshot. It locks when it cannot create a snapshot or when you specify TABLOCK.
How do I Check a Busy Server Safely?
Use PHYSICAL_ONLY for frequent checks and a full check at longer intervals.
How do I Estimate TempDB Space?
Execute DBCC CHECKDB WITH ESTIMATEONLY. It shows the space and performs no check.
Which Repair Option is Safe?
REPAIR_REBUILD makes repairs without data loss. REPAIR_ALLOW_DATA_LOSS is an emergency last resort.
Key Takeaways
- No tool replaces DBCC CHECKDB but options reduce its impact.
- PHYSICAL_ONLY suits frequent checks on production servers.
- Restoring from a good backup beats every repair option.
- Page restore fixes a few pages without a full restore.
- REPAIR_ALLOW_DATA_LOSS is an emergency last resort.
Sources and References
This guide follows Microsoft documentation. Commands are untested.
- DBCC CHECKDB (Microsoft Learn) learn.microsoft.com
- Restore Pages (Microsoft Learn) learn.microsoft.com