Sql-server – dbcc checktable/checkdatabase errors after database restore

dbcc-checkdbrestoresql-server-2005sql-server-2012

Following on from this question Error when creating an index in SQL Server 2012 we have been having problems restoring our live database back to test.

Our current production database is SQL 2005 running on Windows Server 2003. Our test environment has just been moved to SQL Server 2012 on Windows 2012 (so that we can test the upgrade before we go live).

We also have local developer databases which are running SQL 2012 on Windows 7.

Each night we do a backup of live and copy it back to our test databases. This was working fine when we were testing on SQL 2005, however now we get consistency errors after restoring the database.
If we run dbcc checkdb on the live database then it returns no errors. After restoring the live database back to test it returns multiple errors on multiple large tables. The same backup file restored to a developers local database does not have these errors.

We have tried taking multiple backups of the live database and each of them has the same problem (although it's different tables each time that are affected).

I have run

RESTORE VERIFYONLY FROM disk= 'z:\Greg.dat'

and it returns

The backup set on file 1 is valid.

As an example, if I run dbcc checktable('audit') then I get:

Msg 8944, Level 16, State 17, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:6063118), row 72. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 1089 and 68.
Msg 8944, Level 16, State 12, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:8892440), row 24. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 13350 and 128.
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:9109957), row 83. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 54834 and 83.
Msg 8944, Level 16, State 12, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:9113642), row 3. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 40 and 27.
Msg 8944, Level 16, State 17, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:6063118), row 72. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 1089 and 68.
Msg 8928, Level 16, State 1, Line 2
Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data): Page (1:6063118) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:6063118) was not seen in the scan although its parent (1:6064626) and previous (1:6063117) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:6063119) is missing a reference from previous page (1:6063118). Possible chain linkage problem.
Msg 8944, Level 16, State 12, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:8892440), row 24. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 13350 and 128.
Msg 8928, Level 16, State 1, Line 2
Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data): Page (1:8892440) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:8892440) was not seen in the scan although its parent (1:8893817) and previous (1:8892271) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:8892441) is missing a reference from previous page (1:8892440). Possible chain linkage problem.
Msg 8944, Level 16, State 16, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:9109957), row 83. Test (VarColOffsets + (int)(sizeof (VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 54834 and 83.
Msg 8928, Level 16, State 1, Line 2
Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data): Page (1:9109957) could not be processed.  See other errors for details.
Msg 8944, Level 16, State 12, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data), page (1:9113642), row 3. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 40 and 27.
Msg 8928, Level 16, State 1, Line 2
Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data): Page (1:9113642) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:9109957) was not seen in the scan although its parent (1:9114805) and previous (1:9109956) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:9109958) is missing a reference from previous page (1:9109957). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:9113642) was not seen in the scan although its parent (1:9114805) and previous (1:9113641) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 990471372, index ID 1, partition ID 72057594787528704, alloc unit ID 72057596473835520 (type In-row data). Page (1:9113643) is missing a reference from previous page (1:9113642). Possible chain linkage problem.
DBCC results for 'Audit'.
There are 34345722 rows in 385619 pages for object "Audit".
CHECKTABLE found 0 allocation errors and 20 consistency errors in table 'Audit' (object ID 990471372).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (Redebiz.dbo.Audit).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The database versions are:

Live: Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Mar 25 2011 13:33:31
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Test: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Developers Local: Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Can anyone suggest a reason why this is happening?

Best Answer

This is really odd. A few suggestions:

  1. Upgrade one of the developer machines to SP1 and see if the issue happens there then too.
  2. Add WITH CHECKSUM to both the backup and the restore to make sure it is not the disk you storing the backup on.
  3. Restore to a (physically) different drive to make sure the problem is not introduced after the restore.
  4. Upgrade Live to the latest CU (http://sqlserverbuilds.blogspot.com/) and rerun CHECKDB on there.

If that all does not fix the problem, reinstall test, as the problem seems to be local to that machine.