DBCC CHECKDB: How to Detect and Handle SQL Server Database Corruption

Database corruption is rare, but when it happens, the difference between a five-minute fix and a lost weekend is how early you find it. SQL Server will happily keep running on a damaged database until a query touches the bad page. DBCC CHECKDB is the tool that finds damage before your users do, and knowing how to run it and what to do with the results is a core DBA skill.

DBCC CHECKDB: How to Detect and Handle SQL Server Database Corruption

What corruption looks like

Most corruption comes from the storage layer: a disk, controller, driver or SAN firmware writing a page incorrectly. SQL Server detects it when a page is read and raises one of these errors:

  • Error 823: the operating system reported an I/O error reading or writing the page.
  • Error 824: the read succeeded, but the page failed a logical check, usually a bad page checksum.
  • Error 825: a read had to be retried before it succeeded. The data is fine for now, but the storage is failing.

All three are serious. Set up alerts for them (see below) so a single occurrence reaches a human.

Make sure page verification is on

Page checksums let SQL Server detect corruption on read. They are the default for new databases, but databases upgraded from very old versions can still use TORN_PAGE_DETECTION or NONE:

SELECT name, page_verify_option_desc
FROM sys.databases
WHERE page_verify_option_desc <> 'CHECKSUM';

ALTER DATABASE [Payroll] SET PAGE_VERIFY CHECKSUM;

A page only gets a checksum the next time it is written, so existing pages are protected gradually.

Run DBCC CHECKDB

DBCC CHECKDB (N'Payroll') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • NO_INFOMSGS hides the per-table progress messages, so only real problems are shown.
  • ALL_ERRORMSGS lists every error instead of stopping at the first 200 per object.

If the output is empty, the database is consistent. CHECKDB runs online using an internal database snapshot, so users can keep working, but it is I/O and CPU heavy.

Options for large databases

  • WITH PHYSICAL_ONLY checks page structure and checksums but skips the expensive logical checks. Much faster, and catches most storage corruption. A common pattern is PHYSICAL_ONLY on weekdays and a full check at the weekend.
  • DBCC CHECKTABLE and DBCC CHECKFILEGROUP let you split a huge database across several nights.
  • Offload to a restored copy. Restore last night's full backup on another server and run CHECKDB there. It checks the backup at the same time, which is a valuable bonus. It does not replace occasional checks on production, because corruption in memory or on the live disk will not appear in an older backup.

How often?

At least weekly for every database, ideally more often for critical ones. The rule of thumb: run CHECKDB more often than you delete old backups, so you always have a clean backup from before the corruption appeared.

When CHECKDB reports errors

Do not panic, and do not run repair first. Work through these steps.

1. Read the last line of the output

CHECKDB ends with a summary and the minimum repair level needed. repair_rebuild means only nonclustered index damage, which can be fixed without data loss. repair_allow_data_loss means data pages are affected.

2. Find out what is damaged

Each error names an object ID and index ID:

  • Index ID 2 or higher: a nonclustered index. Drop and recreate it (or rebuild offline). No data is lost, because the index can be rebuilt from the table.
  • Index ID 0 or 1: the heap or clustered index, which is the actual data. This is where you need backups.
SELECT OBJECT_SCHEMA_NAME(123456789) AS schema_name,
       OBJECT_NAME(123456789)        AS table_name;

3. Check suspect pages

SQL Server records pages that failed with 823 or 824 errors:

SELECT database_id, file_id, page_id, event_type, error_count, last_update_date
FROM msdb.dbo.suspect_pages;

4. Restore from backup

For damaged data pages, restoring is the correct fix. Options:

  • Page restore: restore only the damaged pages from a full backup, then apply log backups. Fast for a handful of pages and available in the full recovery model (online in Enterprise edition).
  • Full restore of the database to a point before the corruption, plus log backups to bring it up to date. Often the simplest when many pages are affected.

5. Repair only as a last resort

ALTER DATABASE [Payroll] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (N'Payroll', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [Payroll] SET MULTI_USER;

The name is honest: it deletes damaged pages and whatever rows were on them to make the database consistent again. It does not tell you which business data disappeared, and it can break referential integrity. Use it only when there is no usable backup, and take a backup of the damaged database first.

6. Fix the cause

Corruption almost always means a hardware or driver problem. Check the Windows system log, storage logs and firmware versions. If you only restore, it will come back.

Alert on corruption errors

EXEC msdb.dbo.sp_add_alert @name = N'Error 823', @message_id = 823,
     @severity = 0, @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_alert @name = N'Error 824', @message_id = 824,
     @severity = 0, @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_alert @name = N'Error 825', @message_id = 825,
     @severity = 0, @include_event_description_in = 1;

-- Then link each alert to an operator, for example:
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 824',
     @operator_name = N'DBA Team', @notification_method = 1;

Alerts on severities 19 to 25 are also worth adding, because they cover other fatal errors.

Azure SQL Database and Managed Instance

Microsoft runs integrity checks and handles storage-level corruption for Azure SQL Database, so you do not schedule CHECKDB there yourself (you can still run it). On SQL Server on Azure VMs, everything in this post applies exactly as on-premises.

Quick checklist

  • Use PAGE_VERIFY CHECKSUM on every database.
  • Run CHECKDB at least weekly; offload to a restored backup where possible.
  • Alert on errors 823, 824 and 825.
  • Rebuild damaged nonclustered indexes; restore damaged data pages.
  • Use REPAIR_ALLOW_DATA_LOSS only when no backup exists.
  • Investigate the storage after any corruption.

Related reading:

Comments

Post a Comment

Popular posts from this blog

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

Azure SQL Database vs SQL Server on Azure VM: Cost Comparison (2026)

Image
If you are moving a SQL Server workload to Azure, the first big decision is where it will run: Azure SQL Database (a fully managed database service) or SQL Server on an Azure Virtual Machine (your own SQL Server, running on a VM you control). Both work well. But they bill you in very different ways, and picking the wrong one can quietly double your monthly bill. This guide breaks down what you actually pay for in each option, where the hidden costs are, and a simple way to decide. Quick answer Choose Azure SQL Database for new applications, single databases, variable or spiky workloads, and teams that do not want to manage patching, backups or high availability. Choose SQL Server on an Azure VM when you need full instance control: SQL Agent jobs across databases, SSIS/SSRS on the same box, OS-level access, specific SQL Server versions, or third-party software installed next to SQL Server. Consider Azure SQL Managed Instance when you want near-full SQL Server compat...
Read more

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more