Corruption bug that people are hitting – Msg 2511 using DBCC DBREINDEX

Here's another corruption bug that people are hitting on 2005 SP2 – something I didn't know about until today.

The situation is this: a table with a non-unique clustered index (i.e. so a hidden uniquifier column is created), and then rebuilt using DBCC DBREINDEX. Sometimes an incorrect query plan is chosen so the rows in the index get sorted incorrectly. A subsequent DBCC CHECKDB (or DBCC CHECKTABLE) will return errors like:

Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 1, partition ID 72057594044678144, alloc unit ID 72057594048348160 (type In-row data). Keys out of order on page (1:16042), slots 47 and 48.

The workaround is to use the new ALTER INDEX command in 2005 to do the rebuild (remember that DBCC DBREINDEX is deprecated). There's also a hotfix available in CU9 (and the upcoming SP3 I guess). Checkout KB 954734 here for details.

Hopefully you won't hit this, but if you do, knowing this may save you a lot of head-scratching.

PS Last post from our 22-day road-trip. Tonight's a date-night in Las Vegas (dinner at Le Cirque, then tickets to Cirque du Soleil's "O") before heading back to our (vacation!) home in Redmond and PASS next week.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.