DBCC CHECKDB Explained: Microsoft's Documentation

What Microsoft documents for DBCC CHECKDB.

Fact-checked Reviewed by Aswin Vijayan Updated November 20, 2026 Based on 1 sources
Summary

This page gives dbcc checkdb explained from Microsoft's own documentation. Microsoft's DBCC CHECKDB page says the command checks the logical and physical integrity of every object in a database. It runs CHECKALLOC, CHECKTABLE and CHECKCATALOG for you, so you do not run them separately. The page calls REPAIR_ALLOW_DATA_LOSS a last resort and recommends restoring from a known good backup instead. It also says to run the PHYSICAL_ONLY option often on production systems and a full check from time to time.

Step 1: DBCC CHECKDB Explained: What It Checks

The page says the command checks page allocation with CHECKALLOC, every table and view with CHECKTABLE, and the system catalog with CHECKCATALOG. It also validates the contents of indexed views, checks consistency between FILESTREAM table metadata and the file system, and validates Service Broker data.

If you leave out the database name, or pass 0, it checks the current database. It does not examine disabled indexes. It works with memory-optimized tables, but validates only disk-based tables.

Step 2: Check Who Can Run It

The page says you need membership in the sysadmin fixed server role or the db_owner fixed database role.

By default, the command uses an internal database snapshot to get a consistent view without blocking users. Since SQL Server 2014, that snapshot uses sparse files created next to the database files, with an _MSSQL_DBCC suffix. They are deleted when the check finishes. If an unexpected shutdown leaves them behind, the page says you can delete them once no check is running.

Step 3: DBCC CHECKDB Explained: Options That Change the Run

The page lists these main options:

  • NOINDEX skips the intensive checks of nonclustered indexes on user tables, which shortens the run. System table indexes are always checked.
  • PHYSICAL_ONLY is a lighter check of page and record headers and allocation consistency. It detects torn pages, checksum failures and common hardware faults. It cannot be combined with repair options.
  • DATA_PURITY checks for invalid or out-of-range column values. These errors cannot be fixed with repair options and must be corrected manually.
  • EXTENDED_LOGICAL_CHECKS adds checks on indexed views, XML indexes and spatial indexes. The page says it can be very slow.
  • ESTIMATEONLY reports the tempdb space needed without running the check.
  • TABLOCK takes locks instead of using a snapshot. It runs faster under heavy load but reduces concurrency.
  • NO_INFOMSGS suppresses informational messages.
  • MAXDOP overrides the server's setting for this statement.

Step 4: Read the Output

A normal run lists row and page counts for each object, plus a final summary. A clean database shows zero allocation errors and zero consistency errors. With NO_INFOMSGS, only a completion message appears. PHYSICAL_ONLY returns a shorter summary.

Each run writes a success or termination message to the error log. The page also says the last clean check date is recorded and written to the event log at startup. When corruption is detected, a stack dump is written to the LOG directory, and only the service account and sysadmins can access it. For a real corruption case, see our guide to repairing a corrupted SQL Server database and our note on page-level corruption.

Step 5: DBCC CHECKDB Explained: Repair Options

The page says REPAIR_REBUILD makes only repairs that cannot lose data, such as fixing missing nonclustered index rows. REPAIR_ALLOW_DATA_LOSS attempts to fix all reported errors and can cause data loss. Both need single-user mode. REPAIR_FAST is kept only for backward compatibility and performs no repairs.

Microsoft's repair guidance is short. Restore from a known good backup instead of repairing. Repairs ignore constraints, so run DBCC CHECKCONSTRAINTS afterward. Run repairs inside a transaction so you can review the results, then commit or roll back. Before REPAIR_ALLOW_DATA_LOSS, copy all database files, including data, log, full-text and FILESTREAM containers. Repair options cannot fix memory-optimized tables, which need a restore. Running CHECKDB first without a repair option shows which repair level is needed.

Step 6: Know the Emergency Mode and Data Loss Warnings

If you set the database to emergency mode, REPAIR_ALLOW_DATA_LOSS can attempt log-based recovery and rebuild the transaction log. The database becomes read-only, logging is disabled, and only sysadmin members can access it. After a log rebuild, the page says there is no full ACID guarantee, so you must check logical consistency and business rules yourself and back up right away.

The page also warns that REPAIR_ALLOW_DATA_LOSS can lose more data than a restore from a good backup. If the database is flagged suspect, see how to fix a suspect SQL Server database. For tools other than the built-in command, see our DBCC CHECKDB alternative guide.

Which Situation Fits You

Use this list as dbcc checkdb explained in short form.

  • Routine health check on a busy server: PHYSICAL_ONLY, run often.
  • Periodic full check: DBCC CHECKDB with no options.
  • You suspect corruption: run it without a repair option first, then read the report.
  • Errors found and a good backup exists: restore, as Microsoft recommends.
  • No usable backup: repair is a last resort, with file copies first.

This Order Is Ours, Not Microsoft's

Microsoft does not give one ranked process. We ordered the steps from the least risky to the most risky: what it checks, who can run it, options, output, repair, then emergency mode. Use it as a checklist, and read the page for your SQL Server version before you run dbcc checkdb explained here on a live database.

What Do These Sources Not Say?

  • The page does not give a fixed schedule. It says the right frequency depends on your business and environment.
  • It does not give run times for a database of a given size.
  • It does not name any third-party repair tool.
  • It does not cover every version-specific behavior in one place, so check your version.

Frequently Asked Questions

What does DBCC CHECKDB do?

It checks the logical and physical integrity of every object in a database.

Do I need to run CHECKALLOC, CHECKTABLE and CHECKCATALOG separately?

No. The page says CHECKDB already includes them.

Is REPAIR_ALLOW_DATA_LOSS safe?

Microsoft calls it a last resort and recommends restoring from a known good backup instead.

How often should I run it?

The page suggests PHYSICAL_ONLY often on production systems and a full check periodically.

What permission do I need?

Membership in the sysadmin fixed server role or the db_owner fixed database role.

Can repair options fix memory-optimized tables?

No. Microsoft says those need a restore from backup.

Key Takeaways

  • DBCC CHECKDB checks every object, so you do not run the sub-checks separately.
  • Restore from a good backup before you try repair.
  • PHYSICAL_ONLY often, a full check periodically.
  • This is dbcc checkdb explained from one Microsoft page, so check your version.

Sources and References

Facts follow Microsoft Learn.

  1. DBCC CHECKDB (Transact-SQL) learn.microsoft.com