DBCC CHECKDB in SQL Server: What It Checks, How Often to Run It and What to Do When It Finds Errors

Database corruption rarely announces itself. A disk controller writes a page incorrectly, a storage driver has a bug, or a power cut interrupts a write, and nothing seems wrong. Users keep working, backups keep succeeding, and the damaged page sits quietly in the data file until a query finally reads it. By then, the same damage may be in every backup you still have.

DBCC CHECKDB is SQL Server’s tool for finding this kind of damage before it finds you. This article explains what it actually checks, how to run it without hurting production, how often to run it, and what to do (and what not to do) when it reports errors.

What Does DBCC CHECKDB Check?

DBCC CHECKDB checks the logical and physical integrity of every object in a database. According to Microsoft’s documentation, a single run:

• Runs DBCC CHECKALLOC, which checks the consistency of disk space allocation structures.
• Runs DBCC CHECKTABLE on every table and view, checking pages, rows and the links between indexes and data.
• Runs DBCC CHECKCATALOG, which checks the consistency of the system catalog (metadata).
• Validates the contents of every indexed view.
• Validates link-level consistency between table metadata and the file system for FILESTREAM data.
• Validates the Service Broker data in the database.

So there’s no need to run CHECKALLOC, CHECKTABLE or CHECKCATALOG separately; CHECKDB covers all of them. By default it also runs column-value (data purity) checks, which find values outside the valid range of their data type, such as an impossible datetime. One exception: in databases upgraded from very old versions, these checks are only switched on after DBCC CHECKDB WITH DATA_PURITY has completed once without errors.

Why DBCC CHECKDB Matters: Backups Copy Corruption Too

The most common assumption we hear is “we have backups, so we’re safe.” But a full backup copies data pages as they are. If a page is already damaged, the backup contains the damaged page. Backups taken with WITH CHECKSUM do verify page checksums while they’re being read, which is valuable, but they don’t perform the structural and logical checks that CHECKDB does.

Run CHECKDB often enough, and when it finds a problem you still have a backup from before the damage, plus the log backups to roll it forward.

How to Run DBCC CHECKDB

The simplest form checks the named database and reports only errors:

DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

If the database is healthy, the command returns only “The command(s) completed successfully.” Without NO_INFOMSGS, it lists row and page counts for every object, which buries any real errors in noise. ALL_ERRORMSGS is already the default on current versions; it’s harmless to keep it in your scripts. Running CHECKDB requires membership in the sysadmin server role or the db_owner database role.

The options that matter

What Happens on a Production Server While CHECKDB Runs?

It isn’t free, though. Plan for these effects:

• Heavy I/O and CPU: CHECKDB reads every allocated page in the database. On a busy server, run it in a low-activity window and limit it with MAXDOP if needed.
• Snapshot files on the data volume: the snapshot is built from sparse files created next to the data files (named <file>_MSSQL_DBCC<id>). They grow as data in the source database changes during the check, so heavy write activity can fill the data volume. If the server shuts down unexpectedly mid-check, these files can be left behind and should be cleaned up.
• tempdb usage: large databases can need significant tempdb space. Use ESTIMATEONLY to see how much.
• Parallelism depends on edition: parallel checking isn’t available in every SQL Server edition, so the same database can take noticeably longer on Standard edition.

If no snapshot can be created, or TABLOCK is used, CHECKDB falls back to locks: an exclusive database lock for allocation checks and shared table locks for table checks. That does block users.

How Often Should You Run DBCC CHECKDB?

Microsoft doesn’t set a single frequency; it depends on the business and the environment. In practice, these principles work:

1. Stay inside your backup retention window. As explained above, this is the rule that matters most. With 14 days of retention, a weekly check gives you at least one clean backup to fall back on.
2. For large databases, combine both modes. Microsoft recommends PHYSICAL_ONLY for frequent use on production systems and a full CHECKDB with no options periodically. A typical plan is a nightly PHYSICAL_ONLY check and a full check at the weekend.
3. Split very large databases. If even a weekend isn’t long enough, you can spread DBCC CHECKTABLE runs for different tables across nights, or check one filegroup at a time with DBCC CHECKFILEGROUP. Run DBCC CHECKALLOC and DBCC CHECKCATALOG separately in that case, since CHECKTABLE doesn’t cover them.
4. Consider offloading to a restored copy. Restoring the latest full backup on another properly licensed server and running CHECKDB there removes the load from production and proves the backup is restorable in the same step. Keep in mind that this only tells you the backup was clean; damage that appears in production after the backup was taken won’t show up, so keep running at least PHYSICAL_ONLY checks on production.

Don’t forget the system databases. master, model and msdb should be checked too; running CHECKDB on master also checks the hidden Resource database. You can set up the schedule with SQL Server Agent jobs or maintenance plans. See our SQL Server Maintenance Plans guide.

When Did CHECKDB Last Succeed? Check Every Database

A scheduled job that exists doesn’t mean it’s working. Jobs get disabled, fail silently or skip new databases. On SQL Server 2016 SP2, 2017 CU9, 2019 and later versions, the time of the last successful check is available directly:

SELECT d.name,
       CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime) AS last_good_checkdb,
       DATEDIFF(DAY, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS datetime), GETDATE()) AS days_ago
FROM sys.databases AS d
WHERE d.state_desc = N'ONLINE'
  AND d.database_id <> 2   -- tempdb
ORDER BY last_good_checkdb;

A value of 1900-01-01 means CHECKDB has never completed successfully on that database. Compare days_ago with your backup retention period; any database above it is at risk. In availability groups, this value shows the last successful check on the primary replica. SQL Server also writes the last clean check to the error log when a database starts: “CHECKDB for database ‘…’ finished without errors on …”. We explain how to read the error log in our SQL Server Error Log guide.

Early Warning Between CHECKDB Runs

CHECKDB runs on a schedule, but corruption can appear at any moment. These settings make SQL Server report damage as soon as it reads a bad page:

• PAGE_VERIFY CHECKSUM: the default for new databases, but databases migrated from very old versions may still show TORN_PAGE_DETECTION or NONE. Check with SELECT name, page_verify_option_desc FROM sys.databases;. After you switch to CHECKSUM, a page only gets a checksum the next time it’s written.
• Alerts for errors 823, 824 and 825: 823 is an operating-system-level I/O error, 824 is a logical consistency error such as a checksum mismatch, and 825 means a read only succeeded after retrying, an early sign of a failing disk. Create SQL Server Agent alerts for these three message numbers and send them to an operator.
• msdb.dbo.suspect_pages: SQL Server records pages that failed with 823 or 824 errors in this table. It’s limited to 1,000 rows, so review and clean it regularly.
• BACKUP … WITH CHECKSUM: verifies page checksums while the backup is taken, and the backup fails if it hits a bad page.

DBCC CHECKDB Found Errors: What to Do (and What Not to Do)

The first instinct is often to run REPAIR_ALLOW_DATA_LOSS. Don’t. As the name says, this option can delete data to make the database consistent, and Microsoft explicitly says it can lose more data than restoring from the last known good backup. Follow this order instead:

1. Save the full output. Write down every error message, the object IDs and index IDs, and the “minimum repair level” line at the end. These decide the next step.
2. Look at where the damage is. If every error is in a nonclustered index (index ID greater than 1), the underlying data is intact; dropping and recreating the index, or rebuilding it, usually fixes the problem with no data loss. Then run CHECKDB again to confirm.
3. Find the cause. Corruption is almost always caused by the I/O path: disk, controller, firmware, driver or storage. Check the Windows system log, storage logs and the SQL Server error log for 823/824/825 errors. Fixing the database without fixing the cause means the damage will come back.
4. Restore from backup. This is Microsoft’s primary recommendation. Under the full recovery model, if only a few pages are damaged, you can restore just those pages from backup (page restore) and then apply the log backups; Enterprise edition can even do this online. For the details, see our SQL Server Restore guide.
5. Repair only as a last resort. If no usable backup exists:
   • First, make physical copies of all database files (.mdf, .ndf, .ldf, full-text catalogs, FILESTREAM folders).
   • Put the database in single-user mode, which every repair option requires.
   • Run the repair inside a transaction (BEGIN TRANSACTION), so you can review the result before you commit or roll it back.
   • After the repair, run DBCC CHECKCONSTRAINTS, because repairs don’t respect foreign keys or other constraints, and take a full backup immediately.

If the corruption has gone as far as the database not coming online at all, see SQL Server Database Stuck in RECOVERY PENDING or SUSPECT?. For a broader look at the causes of corruption, see SQL Server Corruption.

Frequently Asked Questions