8

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

Improve this question
4
  • 1
    I like your approach. Makes sense to me. Perhaps keep the damaged database along side, as insurance... Commented Feb 5, 2010 at 19:48
  • Also, when things settle down, plan your 2008 or 2008 upgrade :-) Commented Feb 5, 2010 at 19:50
  • (that is, 2005 or 2008 upgrade) Commented Feb 5, 2010 at 19:51
  • +1 for having a valid and very suitable recovery plan Commented Feb 5, 2010 at 20:29

2 Answers 2

Reset to default
2

Your recovery solution is the text book way to proceed. Assuming you have appropriate backups and provided you can backup the transaction log for the corrupt database, then your strategy is the text book one to implement.

Before proceeding however have you considered the possibility of recreating solely the affected table?

Sometimes you can get away with creating an exact copy of the affected table by doing a

select * into NewTableFromOld from DamagedTable

Then just drop/swap the damaged table with the new, remembering to add appropriate constraints and indexes.

Improve this answer
4
  • Thanks for the response. You approach looks good, but the concern is, considering that the table has 461 million rows of data, what would be the impact on production when I do a select *? Wouldn't that aquire a lock and hit performance? Commented Feb 6, 2010 at 7:24
  • Not if you use the with (nolock) query hint no. However, you may wish to validate that nothing has changed between source and target table whilst you took the copy. This would likely be required during the table build anyway though if you did not stop the app from making changes. There will be disk performance overhead in such an operation. Commented Feb 6, 2010 at 7:32
  • Personally I would try this approach first, you can do it with the nolock option for testing, you can check the time required also. If it works like it should, I would then put the db in single user mode while doing it again, to make sure that no changes happen while the process is running. Downtime will be much shorter during this approach, if it works. Commented Mar 17, 2010 at 12:13
  • If you are going to do this, I would recommend creating the table first (by scripting out the original table). Then use "INSERT INTO newTable SELECT" vs the "SELECT INTO". You can run into performance issues with a "SELECT INTO". Commented Jan 14, 2011 at 20:45
0

I'd try bulking out the data to file first and then bulk it back in to a new table. SELECT INTO isn't appropriate (IMO) for that number of records...

Improve this answer

You must log in to answer this question.