CHECKDB (Part 8): Can repair fix everything?

I was teaching at a Microsoft-internal class last week and there was a discussion on what corruptions can't be repaired using DBCC. At the same time, several threads popped up on forums and newsgroups with people hitting some of this unrepairable corruptions so I thought that would make a good topic for the next post in the CHECKDB series.

Before anyone takes this the wrong way - what do I mean by "can't be repaired"? Remember that that purpose of repair is to make the database structurally consistent, and that to do this usually means deleting the corrupt data/structure (that's why the option to do this was aptly named REPAIR_ALLOW_DATA_LOSS - see this post for more explanation on why repair can be bad). A corruption is deemed unrepairable when it doesn't make sense to repair it given the damage the repair would cause, or the corruption is so rare and so complicated to repair correctly that it's not worth the engineering effort to provide a repair. Remember also that recovery from corruptions should be based on a sound backup strategy, not on running repair, so making this trade-off in functionality makes perfect sense.

Here's a few of the more common unrepairable corruptions that people run into along with the reasons they can't be repaired by DBCC.

PFS page header corruption

An example of this is on SQL Server 2005:

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:13280496) has invalid PFS_PAGE page header values.
Type is 0. Check type, alloc unit ID and page ID on the page.

CHECKDB uses the PFS pages to determine which pages are allocated - and so which pages to read to drive the various consistency checks. The only repair for a PFS page is to reconsutruct it - they can't simply be deleted as they're a fixed part of the fabric of the database. PFS pages cannot be rebuilt because there is no infallible way to determine which pages are allocated or not. There are various algorithms I've experimented with to rebuild them with optimistic or pessimistic setting of page allocation statuses and then re-run the various consisteny checks to try to sort out the incorrect choices, but they all require very long run-times. Given the frequency with which we see these corruptions, and the engineering effort required to come up with an (imperfect) solution, I made the choice to leave this as unrepairable.

Critical system table clustered-index leaf-page corruption

An example of this is on SQL Server 2000:

Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:18645) with latch type SH. sysindexes failed.

And on SQL Server 2005:

Msg 7985, Level 16, State 2, Server SUNART, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:51) with
latch type SH. Check statement terminated due to unrepairable error.

In a previous post I described why how and why we do special checks of the clustered indexes of the critical system tables. If any of the pages at the leaf-level of these indexes are corrupt, we cannot repair them. Repairing would mean deallocating the page, wiping out the most important metadata for potentially hundreds of user tables and so effectively deleteing all of these tables. That's obviously an unpalatable repair for anyone to allow and so we don't do it.

Column value corruption

Here's an example of this on SQL Server 2005:

Msg 2570, Level 16, State 3, Line 1
Page (1:152), slot 0 in object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type "In-row data"). Column "c1" value is out of range for data type "datetime".  Update column to a legal value.

This is where a column has a stored value that is outside the valid range for the column type. There are a couple of repairs we could do for this:

1. delete the entire record
2. insert a dummy value

#1 isn't very palatable because then we've lost data and its not a structural problem in the database so doesn't have to be repaired. #2 is dangerous - what value should we choose as the dummy value? Any value we put in may adversely affect business logic, or fire a trigger, or have some unwelcome meaning in the context of the table - even a NULL. Given these problems, we chose to allow people to fix the corrupt values themselves.

Metadata corruption

Here's an example of this on SQL Server 2005:

Msg 3854, Level 16, State 1, Line 2
Attribute (referenced_major_id=2089058478) of row (class=0,object_id=2105058535,column_id=0,referenced_major_id=2089058478,referenced_minor_id=0) in sys.sql_dependencies has a matching row (object_id=2089058478) in sys.objects (type=SN) that is invalid.

This example is relatively benign. There are other examples that will cause CHECKDB to terminate - not as bad as the critical system table corruption example above, but enough that CHECKDB doesn't trust the metadata enough to use it to drive consistency checks. Repairing metadata corruption has the same problems as repairing critical system table corruption - any repair means deleting metadata about one or more tables, and hence deleting the tables themselves. It's far better to leave the corruption unrepaired so that as much data as possible can be extracted from the remaining tables.

Summary

Repair can't fix everything. You may end up having to perform manual and time-consuming data extraction from the corrupt database and losing lots of data because of, say, a critical system table corruption. Bottom line (as usual) - make sure you have valid backups so you don't get into this state!

Comments

• Anonymous
  May 30, 2007
  In the case of a missing row in sys.sql_dependencies referencing a row in sys.columns, which object needs to be recreated?  There are two object_id's referenced.

• Anonymous
  May 30, 2007
  Disregard my previous comment.  Once I started looking up every object_id, it became apparent that it was only the object_id that referenced the sys.sql_dependencies, which turned out to be store procedures that were having dependency issues....which we all knew was happening in SQL 2000 anyway.  Cheers.

• Anonymous
  May 19, 2009
  In this post, we would have a look at the differences between DBCC CHECKTABLE and DBCC CHECKDB. We would

• Anonymous
  April 07, 2011
  The comment has been removed

• Anonymous
  June 05, 2012
  Use program way recovery www.sqlserver.fixtoolbox.com Tool repair sql database any version starting with MS SQL Server 2000

• Anonymous
  April 27, 2014
  The comment has been removed

• Anonymous
  May 04, 2014
  SQL recovery software has ability to recover data from SQL databases with corrupt GAM/SGAM/PFS/FCB pages, metadata corruption, suspect mode, consistency corruption many more. It repairs & restores SQL database objects like tables, stored procedures, keys, indexes, views etc. Software supports SQL server 2012, 2008 R2, 2008, 2005, & 2000. www.mssqldatabaserecovery.com

• Anonymous
  October 25, 2014
  The comment has been removed

• Anonymous
  December 02, 2014
  Visit this site, it can be helpful: www.filerepairforum.com/.../569-i-have-a-problem-how-do-i-fix-a-corrupted-database-file Or you can visit: SQL Server Fix Toolbox http://www.fixsqldatabase.com/