SQL Server – Consistency Errors in Database Table After Upgrade to SQL Server 2012

sql server

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.

Best Answer

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.

Related Question