Repairing inconsistent pages in database
- by Raj
We have a SQL 2000 DB. The server crashed due to Raid array failure. Now when we run DBCC CHECKDB, we get an error that there are 27 consistency errors in 9 pages.
When we run DBCC PAGE on these pages, we get this:
Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 1397580017, index ID 2, page (1:8404521). Test (m_freeCnt == freeCnt) failed. Values are 2 and 19.
Msg 8939, Level 16, State 108, Line 1
Table error: Object ID 1397580017, index ID 2, page (1:8404521). Test (emptySlotCnt == 0) failed. Values are 1 and 0.
Since the indicated index is non-clustered and is created by a unique constarint that includes 2 columns, we tried dropping and recreating the index. This resulted in the following error:
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 2. Most significant primary key is '3280'.
The statement has been terminated.
However running
Select var_id,result_on
from tests
group by var_id,result_on
having count(*)>1
returns 0 rows.
Here is what we are planning to do:
Restore a pre-server crash copy of the DB and run DBCC CHECKDB
If that returns clean, then restore again with no recovery
Apply all subequent TLOG backups
Stop production app, take a tail log backup and apply that too
Drop prod DB and rename the freshly restored DB to make it prod
Start prod app
Could someone please punch holes in this approach? Maybe, suggest a different approach? What we need is minimum downtime.
SQL 2000
DB Size 94 GB
The table that has corrupt pages has 460 Million+ rows of data
Thanks for the help.
Raj
