1

I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

Yesterday, I got two entries in "suspect_pages" for the same database. One of event type 1 and one of type 2

1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

2 = Bad checksum.

database_id file_id page_id eventtype   error_count last_update_date
8           1       1482057 1           1           2019-11-14 14:40
8           1       1482057 2           1           2019-11-14 14:40

I found the object related and they both point to the same table on the database.

DBCC TRACEON (3604); DBCC PAGE (8, 1, 14823057, 0); DBCC TRACEOFF (3604);

I had a valid backup of before the corruption and couldn't afford a down time so I took a backup of the corrupted database, restored my backup on a new name. I dropped the corrupted table and then recreated it from the valid backup.

Today, I restored the corrupted database backup that I took yesterday on a test server and when I run a full checkdb, it detects no corruption.

DBCC CheckDB() WITH No_INFOMSGS, ALL_ERRORMSGS

How is it possible that the backup I took from a corrupted database (according to suspect_pages) doesn't have any problems? Can those entries in suspect_pages be a false positive?

Database Compatibility Level is 130 (SQL 2016) Our SQL Server is running on Windows Server 2012.

  • 1
    can you post the event_type as well repaired, restored, or deallocated - 5,4 or 7 ? That will explain why you get a clean checkdb. – Kin Shah Nov 15 '19 at 18:11
  • event_type is 1 and 2, I'll correct my question so that it's easier to see – Danielle Paquette-Harvey Nov 15 '19 at 18:12
1

So I wrote to Brent Ozar and this is the answer I got from him. It's the best answer I've got so far so I'll put it here:

here's exactly what I do when SQL Server reports corruption: https://brentozar.com/go/corruption

Suspect_pages doesn't do false positives, but the corruption may have been repaired since (or may have only been corrupt temporarily, like if storage had a transient error when you went to go read it the first time.) Definitely follow that checklist and star tthe support call with MS.

Hope that helps! Brent

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.