Corrupted Table

Big Problems

[woop01]

(SQL Server 2000, SP3)

We recently moved a DB over from a hard drive we suspected was becoming corrupt. Well, guess what, it was corrupt. We have a backup from right before the move and we have the original MDF files from right before the move. Both of those files have this problem.

We’ve been able to repair all of our tables with dbcc checktable – repair_allow_data_loss except for one. This last table has 1.4 million rows and if you run checktable allowing for data loss, that drops to 158k rows which is unacceptable. The extremely frustrating thing is we have even figured out which rows are causing the problems. We just cannot delete them because we get this message…

-----------------------------------------
Server: Msg 21, Level 24, State 1, Line 1
Warning: Fatal error 823 occurred at Feb 2 2005 9:39PM

Connection Broken

Now I know Level 24 means some serious stuff. However, is there a way to delete rows that are corrupted in this manner?

[Zaphod Beeblebrox]

How about creating a new table and copying the correct rows into that?

[woop01]

Thank you for the suggestion. You posted that about two minutes after we got done doing it.

We duplicated the schema of the table and wrote a program to copy the '1.39' million rows that weren't corrupted to it then switched the names of the tables. It worked like a charm. Well, a charm that took until 4:00 AM to finish but still a charm.

[Zaphod Beeblebrox]

:o)