DBCC CHECKDB: Just Because It’s Quiet Doesn’t Mean It’s Safe

Stop. Don’t panic.

You just ran DBCC CHECKDB for the first time in a while (or maybe ever) and saw something you didn’t expect: the word corruption.

Take a breath.

Don’t detach the database.
Don’t run REPAIR_ALLOW_DATA_LOSS.
Don’t reboot the server or start restoring things just yet.

There’s a lot of bad advice floating around from old blogs, well-meaning forum posts, and even some popular current LinkedIn threads. Some of it might’ve been okay 15 years ago. Some of it is dangerous.

Let’s dig in.

What Corruption Really Means

When SQL Server says there’s corruption, it’s not talking about “bad data” like wrong numbers or missing values. It means it found internal structures that are damaged. The kind that can cause queries or even make your database unusable.

This could be:

• Broken data or index pages

• Allocation inconsistencies (GAM, SGAM, PFS pages)

• Corrupt system metadata

• Problems in the transaction log.

This isn’t a performance problem.
It’s a data integrity problem. If left untreated, it can get worse.

How Does Corruption Happen?

Even if your server is well-configured, corruption can still creep in. Common causes include:

• Failing disks or controllers (especially SANs and older SSDs)

• Disk subsystems lying about successful writes

• Power outages or hard shutdowns.

• Sometimes SQL Server itself has bugs that cause corruption – especially in RTM versions.

• Snapshot or backup software interfering at the file level

• Antivirus software scanning .mdf, .ldf, or .ndf files directly

Some of these things leave no obvious signs. This is why running CHECKDB regularly is so important.

What DBCC CHECKDB Actually Does

When you run DBCC CHECKDB, SQL Server performs a deep consistency check of your database:

• Every table, every index, every system structure

• Logical and physical page consistency

• Allocation integrity

If possible, SQL uses a snapshot to avoid locking the database.

What it doesn’t do:

• Fix anything (unless you tell it to)

• Prevent corruption

• Run automatically (unless you set it up)

How Often Should You Run It?

Ideally: once per week, at minimum.

• Schedule it in a SQL Agent job, off-hours.

• Save the job output to file or table so you don’t miss warnings.

• Set up an email alert for failures of this job (as well as corruption alerts for error 823-825)

If CHECKDB takes too long or hits your performance too hard, you can offload the work.

Offload CHECKDB with Test-DbaLastBackup

If you’re taking backups regularly (you are, right?), you can use Test-DbaLastBackup from the dbatools.io PowerShell module to verify database consistency (and restorability) without touching production.

This command:

• Restores your most recent backup to another SQL instance

• Runs DBCC CHECKDB against the restored copy

• Confirms both restorable state and internal consistency 

Test-DbaLastBackup -SqlInstance "TestRestoreSQL" -Destination "TestRestoreSQL" -Database "YourDatabase"

It’s a great way to validate backups and run CHECKDB in a lower-impact environment.
Not a replacement for CHECKDB in production, but a powerful supplement when time or resources are tight.

• Consider running CHECKDB on a secondary replica if you’re using Availability Groups.

• If CHECKDB fails due to size or takes too long, it’s even more important to find time and a strategy that works.

What to Do If It Finds Corruption

1. Read the output carefully.
   It tells you which object is affected and how.

2. Run CHECKDB again to confirm.
   Temporary issues can happen, especially on shared storage.

3. Do not detach the database.
   Doing so loses the ability to investigate further.

4. Check your backups.
   Can you restore from before the corruption appeared? This is the first thing Microsoft will tell you when you call support.

5. If you are really lucky the corruption might be in a non-clustered index, and dropping/recreating that index may solve it for now.

6. Still stuck? Read this from Brent Ozar: DBCC CHECKDB Reports Corruption? Here’s What to Do

About REPAIR_ALLOW_DATA_LOSS

That command does exactly what it says: it removes damaged pages and objects to make the database consistent again—even if that means losing real data.

Use it only when:

• You have no usable backup

• You’ve consulted with your team and accepted the risk (get that in writing from your manager/CTO)

• You’ve tried every other recovery option

If you're not sure what it’s going to delete (if anything) and the impact, you’re not ready to run it. This is the sort of thing that can get you fired. So is not having backups.

How to Check When DBCC CHECKDB Was Last Run

This script gives you the last successful CheckDB execution for each database:

SELECT 
    name AS DatabaseName,
    DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS LastCheckDBSuccess
FROM 
    sys.databases
WHERE 
    state_desc = 'ONLINE'
ORDER BY 
    LastCheckDBSuccess DESC;

 If the date is blank, it has never been run.

The Bottom Line

Corruption doesn’t announce itself with a trumpet. You only know it’s there if you go looking.

CHECKDB gives you an early warning. It’s not glamorous, but it’s essential, especially in environments without a dedicated DBA watching for signs of trouble.

If you’re not running it, you’re flying blind.

If you don’t know what to do when it finds something, now’s the time to prepare.

Don’t panic. But don’t ignore it either.

Are you ready for a database disaster? Take the quiz!

SQL tidBITs:

Run this query now to see if any corruption has already been silently captured:

Select * from msdb.dbo.suspect_pages

LINKS

Please share with everyone that read THAT thread on LinkedIn suggesting ‘repair_allow_data_loss’ as a first choice. Horrible idea.