Sql-server – Entries in suspect_pages but checkdb shows no error

The following is a compilation of results that I read up on. You will find vastly more information in the linked blogs and documents.

First, it can happen that DBCC CHECKDB won't detect inconsistencies if you turn off checksum or torn_page verification. A quote from Paul Randal:

You're right - if torn-page or checksum isn't turned on then there's nothing that can be detected as far as page protection options are concerned. CHECKDB may still pick up on corruptions that it finds from doing all the consistency checks that it does - but it won't see corruptions in the middle of data values, for instance.

Ha - that's the bummer about turning on page checksums - nothing happens until a page is read in, changed, and written back out. The only way to force pages to get checksums is to make them change - e.g. through rebuilding all your indexes, whcih may be unpalatable - there's no 'touch' tool out there at all.

The above situation can hit you, if you upgraded a database from SQL Server 2000 or before to 2005 or later. You then need to manually enable page checksums with ALTER DATABASE to get them active. But then the 2nd paragraph of the above quote kicks in and might trouble you.

BACKUP WITH CHECKSUM will detect checksum inconsistencies, but only if the page already had a checksum written to it, when it is being backed up. Normally DBCC CHECKDB also detects these errors, so it's not a good idea to "use BACKUP WITH CHECKSUM to replace DBCC CHECKDB".

Now there is a second possibility for DBCC CHECKDB not to show any inconsistencies, even if there are some. For this I'm just quoting again Paul Randal in "Misconceptions around corruptions: can they disappear?":

So what about the disappearing corruptions? This gets into how consistency checks work. Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. Don't get confused between reserved and allocated - I explain that in the first misconceptions post here. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is allocated at the time a DBCC CHECKDB runs, but is then deallocated by the time the next DBCC CHECKDB runs. The first time it will be reported as corrupt, but the second time it's not allocated, so it isn't consistency checked and won't be reported as corrupt. The corruption looks like it's mysteriously vanished. But it hasn't - it's just that the corrupt page is no longer allocated. There's nothing stopping SQL Server deallocating a corrupt page - in fact, that's what many of the DBCC CHECKDB repairs do - deallocate what's broken, and fix up all the links.

I don't have a final answer to your question, but as DBCC CHECKDB only checks allocated pages it won't show inconsistencies in deallocated pages. The only situation I can imagine now is that BACKUP also backups those deallocated pages showing potential checksum errors that were skipped by DBCC CHECKDB.

If the sequence is:

1. DBCC CHECKDB reported errors.
2. REPAIR_REBUILD fixed the database, no errors reported.
3. DBCC CHECKDB reporting errors again.

You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP!

• Take a tail-log backup.
• Restore last known good full backup and diff/log sequence to a different server.
• Switch service to the second server.
• Get a fresh cuppa and start diagnosis on the problem server.

Addendum: Root cause of the problem:

as it turned out, I found that as we are using Diskeeper, we are (for corporate reasons) still using an old version : 14.0.896. With this version we run into messages like :

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15100) to SQL Server during a write at offset 0x000000010a4000 in file 'D:\Application-Data\MSSQL\Data\<Database>.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Server <Server>, Line 1
During redoing of a logged operation in database '<Database>', an error occurred at log record ID (2349664:1503:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 1823, Level 16, State 1, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Server <Server>, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Server <Server>, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Server <Server>, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details

Found an explanation on Paul Randal's blog here

Link to DiskKeeper Fix found here

Interesting Diskeeper white paper on database defragmentation here