Consistency errors in database table after upgrade to SQL Server 2012

Question

I keep getting a weird problem with some databases after upgrade to SQL Server 2012 (either from SQL Server 2005 or from SQL Server 2008 R2).

The backup (.bak) is fine on older version. I do DBCC CHECKDB on SQL Server 2008 R2 and everything is fine. I create a .bak file, restored it on SQL Server 2012 and run DBCC CHECKDB -> 2 consistency errors in table.

Using suggestions from the article "Upgrading To SQL 2012: Ten Things You Don’t Want To Miss" I tried DBCC CHECKDB WITH DATA_PURITY; before and after upgrade.

Is this a know/common issue?

The error details are:

Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 1. Column 'ID' was created NOT NULL, but is NULL in the row.

Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 2. Column 'ID' was created NOT NULL, but is NULL in the row.

Obviously I checked the table in question and the ID column doesn't have any rows with NULL values.

• Shanky: Have you applied latest service pack on SQL Server 2012, that would go without saying.
• Nux: Yes, seems so. I'm on 11.0.6020 so SQL Server 2012 SP3.
• Shanky: So index id=2 would basically be a Non Clustered Index and its quite likely that the issue would go away after you drop and recreate the index. Can you please try it.
• Nux: Only index on ID column in that table is PRIMARY KEY NONCLUSTERED . And as that table has foreign keys references I'm not able to simply drop and recreate it. DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is much simpler -- fixes the issue and no data seem to be actually lost... Still I don't know why that really happens after upgrade. Seems like the upgrade process creates the error.
• Shanky: DO NOT USE repair_allow_data_loss it may remove data and business constraints and you would end up with inconsistent tables with relations dropped. And it would not tell you what you did. If you want to try this take backup of current database restore it on other server and then run the repair_allow_data_loss. And does checkdb actually asked you to run the repair or its is your thought ?

Answer 1

Hm... That's interesting...

So the index is somehow corrupted before the upgrade and when you see an error as above you can both fix this before and after upgrading, but only after upgrading DBCC will detect the problem.

Steps:

1. Figure out which index is failing
   1. Run DBCC CHECKDB to get a column name.
   2. Find index that spans over this column (for me it was a primary key).
2. Disable index: ALTER INDEX [my index name] ON [my table name] DISABLE; (some foreign keys might be disable along with it, don't worry about that).
3. Rebuild/re-enable index: ALTER INDEX [my index name] ON [my table name] REBUILD;.
4. Run DBCC CHECKDB; to make sure everything is fine.

That worked for me but YMMV. Again, you can do above in MS SQL 2008 R2 and then restore fresh backup on MS SQL 2012 or do above when already on MS SQL 2012.