How to Check Database Corruption in SQL Server

SQL Server corruption rarely announces itself politely. A query fails, a database flips to suspect or a strange error shows up in the log. The good news is that SQL Server leaves a clear trail, so you can confirm corruption in minutes if you know where to look.

This guide shows the fast checks first, the error log and the suspect_pages table, then the full DBCC CHECKDB check and the settings that make sure SQL Server is watching for you. It ends with the safe order to follow once you find a problem.

What are the signs of database corruption?

Corruption usually shows itself before you go looking. The common signs are a query that fails with an I/O or consistency error, a database that will not come online or errors 823, 824 and 825 piling up in the log. A database stuck in suspect or recovery pending is a strong sign too. If yours is in that state, our guide on the SQL Server recovery pending state walks through it.

Any one of these is enough reason to run the checks below. The sooner you confirm it, the more likely you still have a clean backup to fall back on.

How to check for corruption in SQL Server

There is no single button. The reliable approach is a few quick checks that together tell you whether pages are damaged and where. Start with the fast ones.

Read the error log for 823, 824 and 825

The error log is the first place corruption shows up. These three error numbers are the ones that matter, so search the current log for them. Each entry names the file and the page involved, which points you straight at the damage.

EXEC sp_readerrorlog 0, 1, '824';
EXEC sp_readerrorlog 0, 1, '823';
EXEC sp_readerrorlog 0, 1, '825';

Query the suspect_pages table

SQL Server records every page that fails a read in a small table in msdb. It is the quickest way to see if any page has already been caught. Event types 1, 2 and 3 are the ones that mean a page is currently bad.

SELECT * FROM msdb.dbo.suspect_pages
WHERE event_type IN (1, 2, 3);

Rows here mean SQL Server has already hit a damaged page during normal work. An empty result is good, but it does not prove the database is clean, since a page is only checked when something reads it. That is what the full check is for.

Run DBCC CHECKDB for the full picture

DBCC CHECKDB is the complete check. It reads the whole database and reports every allocation and consistency error it finds. A clean run ends with a line reporting zero errors. For the full syntax, the options and how to read the output, see our guide on how to run DBCC CHECKDB in SQL Server.

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Make sure PAGE_VERIFY is set to CHECKSUM

This is the setting that lets SQL Server catch bad pages on its own. With CHECKSUM on, SQL Server checks each page when it reads it and raises an error the moment one fails. If it is set to NONE or the older TORN_PAGE_DETECTION, you are flying blind. Check it across all databases, then turn it on where it is missing.

SELECT name, page_verify_option_desc FROM sys.databases;

ALTER DATABASE YourDatabase SET PAGE_VERIFY CHECKSUM;

Verify your backups too

Corruption can ride along into your backups, so a backup is only as good as its last check. Take backups with CHECKSUM, then test them with RESTORE VERIFYONLY. A backup that passes is one you can trust on the worst day.

BACKUP DATABASE YourDatabase TO DISK = 'D:\Backups\YourDatabase.bak' WITH CHECKSUM;

RESTORE VERIFYONLY FROM DISK = 'D:\Backups\YourDatabase.bak' WITH CHECKSUM;

What causes SQL Server corruption?

Almost all of it comes from below SQL Server, in the storage layer. The usual culprits sit in the hardware: a failing disk, a faulty storage controller, a bad driver or firmware. Power lost in the middle of a write is another. SQL Server bugs cause corruption far less often than people assume. That is why the error codes point at I/O and why the fix usually starts with the hardware, not the database.

What to do when you find corruption

Confirming corruption is the start of recovery, not the end of the job. The order you follow now decides how much data you keep.

When the database will not come online, the corruption is severe or there is no usable backup, a recovery tool reads the MDF and NDF files directly and pulls the data out, including objects a repair would have deleted. The SQL Database Recovery Software works on a copy of the file, so the original stays safe while you recover.

Download Now
Purchase Now

Going deeper: how SQL Server spots a corrupt page

Most articles stop at “run CHECKDB.” They never explain how SQL Server knows a page is bad. That is what tells you how serious a finding is. This section goes there. It is more technical, so it sits at the end.

The page checksum and why corruption shows up late

With PAGE_VERIFY CHECKSUM on, SQL Server writes a checksum into each 8 KB page when it saves the page to disk. The next time it reads that page, it recomputes the checksum and compares. If the two do not match, the page changed on disk while nobody was looking, so SQL Server raises an error. The catch is timing. A page is only checked when something reads it, so a page that rotted last month stays silent until a query touches it. This is why corruption often looks sudden when the damage is really old. It was always there. Today is just the first read.

What 823, 824 and 825 really tell you

These three are not random numbers. They are a story about your storage. Reading them right saves hours. Error 823 means the read or write itself failed at the operating system level, so Windows could not complete the I/O. Error 824 means the I/O succeeded but the page that came back failed a consistency check, like a bad checksum or a torn page. From SQL Server 2005 on, the engine retries a failed read up to four times before it gives up and raises 823 or 824. Error 825 is the one to fear precisely because it looks harmless. It is a low-severity note that a read finally succeeded after failing a few times. No query fails, no alarm rings, just a quiet line in the log. It is the disk warning you before it fails for real. Microsoft documents the family in its page on error 824.

Reading suspect_pages like an investigator

The suspect_pages table is a short history of every bad page SQL Server has hit. The event_type column is the key. Type 1 is an 823 error or a bad page ID, type 2 is a bad checksum and type 3 is a torn page. Those three mean a page is currently damaged. Higher types are the repair history: a page that was restored, repaired by DBCC or deallocated. So the table tells you not just what is broken now, but what has been broken and fixed before, which is often the real clue that a disk is on its way out.

How a recovery tool checks pages on its own

This is the part that matters when SQL Server has given up on the file. A recovery tool does not ask SQL Server whether a page is good. It opens the MDF, reads each 8 KB page and validates the checksum itself, the same comparison SQL Server makes but outside the engine. Because it controls the read, it can work around a page SQL Server refuses to serve and lift the rows from the pages on either side, recovering data that a repair would simply deallocate. For how a tool reads the raw MDF page structure, see the technical section in our guide on DBCC CHECKDB taking a long time. For the repair levels and what they delete, see our guide on running DBCC CHECKDB.

Final word

So checking for corruption in SQL Server is really a habit, not a single command. Read the log for 823, 824 and 825, query suspect_pages, run CHECKDB on a schedule and keep PAGE_VERIFY on CHECKSUM so the engine watches every read. Catch it early and you fix it from a backup. Catch it late and you are recovering pages instead.

So here is the question worth asking before the next bad page shows up. Are those 825 read-retry warnings already sitting in your log, waiting for someone to notice?