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:
delete the entire record
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.
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.
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!