1

I am getting consistency errors while running DBCC CHECKDB on a table that has corrupted rows on SQL Server 2014. I am running into corruption errors after the first 30,000 rows (total 1.4 million (14 lakh) rows).

I want to try to bcp the data out until I get to the corruption. I want to skip the corrupted rows. I want to bcp after the corruption range and minimize the data loss.

How can I do it? Please explain in detail.

See also my previous question: Consistency Errors running CHECKDB on a couple of tables in SQL Server 2014

6

First, here's my checklist on how to tackle corruption issues. It's a big deal - bigger than I can cover here - but based on the sounds of this, I don't think you've covered some of the basics (like trying to restore from backup, or making sure you've even got backups.)

When you start getting corruption errors, every time you access storage, you may be making the problem worse. For example, I dealt with one broken storage array where the more we read data, the more it became corrupted. (I'd read one table and it was fine, and then fifteen minutes later, it was hosed too.) Just like a haunted house, you need to get off that storage device as quickly as possible, and tackle your recovery efforts somewhere else.

Once you're on another storage device, if you can isolate the corruption down to a single table (which isn't likely, but you're hinting that you've done that), then:

  • In CHECKDB's output, identify which indexes are impacted (you can see via the index ID - 0 is a heap, 1 is a clustered index, and 2 & higher are nonclustered indexes)
  • If you have corruption in index 0/1, then for bonus points, copy data out from each index separately. You may be able to get some of the columns back for the corrupted rows in the clustered index. (If you have corruption across multiple nonclustered indexes, you may be able to get all of the rows from some nonclustered indexes, but not others.)
  • If you can identify which pages are affected, then you'll need to design your WHERE clauses to work around the corrupted pages. (SQL Server won't gracefully skip corrupt pages - it's up to you to design your query to stop before it encounters corruption.)

Think about a phone book, for example, organized by last name, then first name. If you know that the page containing Ozar, Brent is corrupted, then you may have to select users up to my last name, but stopping before that - and then run a second query for users with a last name higher than mine.

Here's the trick, though: corruption is going to strike all over the table. You're going to be doing a ton of work trying to get around different corrupt pages.

It's not easy, and that's why I wrote my checklist - and why it doesn't try to tackle this portion. You're talking about days, if not weeks, of work when the corruption is widespread.

1

Honestly, my recommendation is to IMMEDIATELY hire a knowledgeable SQL Server consulting person/firm to assist. Depending on the data your company could literally be at risk here. And if you do ANYTHING (especially dbcc stuff, rebuilds, etc) to the db other than read-only stuff you are running the risk of making things worse! SQLSkills is probably the best resource here, but there are others that could help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.