Four Corruption Errors message that cann't repair by DBCC

SQL Server database supports various DBCC commands which are used to check database consistency. DBCC commands also helps to resolve SQL database corruption errors & repair corrupt SQL Server database. There are some errors which is not repairable by DBCC Command. Here are the list:

1) PFS page header corruption:

Full error message in SQL Server 2005:

"Msg 8946, Level 16, State 12, Line 1

Table error: Allocation page (1:24264) has invalid PFS_PAGE page header values.

Type is 0. Check type, alloc unit ID and page ID on the page."

Solution:

This error is unrepairable by CHECKDB command. There is only two solutions to resolve this error first is to restore from backup & second is to reconstruct PFS page.

Reconstruct PFS page is very tough or we can say that it is impossible because there is no infallible way to determine which pages are allocated or not.

2) Critical system table clustered-index leaf-page corruption

Full error message in 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.”

Full error message in 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."

Solution:

We cannot repair system table clustered-index leaf-page corruption error through DBCC CHECKDB command. If we try to repair these pages, it means deallocating the page, which is not possible.

3) Column value corruption

Full error message in 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."

The error indicates that the column has a stored value that is out of range for column type.

Solution:

This error is unrepeatable by CHECKDB command.

I would like to recommend two more solutions to resolve this issue.

1. Delete the entire record
2. As error message indicate that insert a dummy value

Through the first step the data can be lost so we can say that the error is not to be repaired.

The second step is very risky because we have no idea about dummy value. If we put any value, it will affect business logic, or some other negative impacts.

4) Metadata corruption

Full error message in 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."

Metadata corruption error are unrepeatable by DBCC because if we will perform any repair options; it means deleting metadata about one or more tables, and hence deleting the tables themselves. It is better to extract to as much data as possible from the remaining tables.