1

On my understanding, regular run of DBCC CHECKDB is a best practice in order to check integrity of the database.

If there comes a time where a corrupted page existed, and no good back up is available, in general, what are the consequences can occur? Nothing? Corruption will spread?

1

DBCC CHECKDB will also run DBCC CHECKALLOC, DBCC CHECKTRABLE, and DBCC CHECKCATALOG.

Running this regularly is extremely important in an effort to detect data corruption. If availability is important, you should consider running this daily. It is possible for backups to contain corrupted data as well. This can easily go unnoticed if the corrupted page(s) isn't accessed. Corruption can occur due to several factors including: storage configurations, power faults, poor cache, etc.

It is possible for corruption to spread, so be prepared! Your system and user databases should be backed up frequently with CHECKSUM and the databases be configured with the Page Verify setting set to CHECKSUM.

I would take a look at Kendra Little's post on the issue.

Brent Ozar also has a post regarding what to do when DBCC CHECKDB reports corruption.

Steve Stedman also has an excellent Weekly Database Corruption Challenge if you wish to attempt repairs.

0

You can repair the corrupted pages using

DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS);

But this will likely cause problems (as ALLOW_DATA_LOSS suggests)

Imagine you have a Person table and a Department table

Person Table
PersonId PersonName DepartmentId
1          John          2
2          Jane          3

Department Table
DepartmentId DepartmentName
1                 HR
2                 IT

lets say the corruption occured on the page that holds the DepartmentId 2 record in the Departments table, that record will be removed from the database and John is now in a department that doesn't exist (referential integrity is broken)

Tn this scenario, before we fixed the corruption, SELECT * FROM Department would error, whereas after running the fix it would work.

You may get lucky in that the corruption only affects indexes, views or other recreatable objects.

As for corruption spreading, if I found corruption, I would be investigating if there were any issues with the disk subsystem. If so I would expect that it would be happening again and would look into getting that fixed.

The best scenario is to have backups and to run CHECKDB frequently enough that you have the last good backup

  • True, on most points, but the OP is talking about corrupt pages explicitly and what the fallout of that is. i.e. would the server stop working? Queries to that page (only) fail? Would it get worse? – scsimon Aug 13 at 16:05

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.