August 5, 2014 at 6:31 pm
I backed up a production database from Data Center A, backed it up across the network and restored it to a SQL Server in Data Center B which is nonproduction. THe restore worked fine. However, a DBCC CHECKDB shows the below errors. If I run a DBCC CHECKDB on the DB in DataCenter A it says zero errors. The SQL Server in DataCenter A is SQL2008R2 SP1 and the one in DataCenter B is SQL2008R2 SP2. My question is that my Storage guys say there is no issues with the back end storage. I say that cannot be... He thinks we need to rule out the SQL Server binaries as the issue. I say this is data corruption. Thoughts?
The SQL Server log when I restored the db says no errors. I know that is a very minor checkdb routine that is run upon a db restore.
CHECKDB for database 'TESTEDB' finished without errors on 2014-08-05 04:15:10.257 (local time).
Here is the output of the checkdb:
Msg 8928, Level 16, State 1, Line 1
Object ID 805577908, index ID 1, partition ID 72057598210211840, alloc unit ID 72057597152591872 (type In-row data): Page (1:91593) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 805577908, index ID 1, partition ID 72057598210211840, alloc unit ID 72057597152591872 (type In-row data), page (1:91593). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 805577908, index ID 1, partition ID 72057598210211840, alloc unit ID 72057597152591872 (type In-row data). Page (1:91593) was not seen in the scan although its parent (1:103922) and previous (1:122896) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 805577908, index ID 1, partition ID 72057598210211840, alloc unit ID 72057597152591872 (type In-row data). Page (1:91597) is missing a reference from previous page (1:91593). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'RPT_Q_ProjectMetrics' (object ID 805577908).
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:476600) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:476600). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:476600), slot 0, text ID 523304960 is referenced by page (1:400692), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:476646) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:476646). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:476646), slot 0, text ID 1045168128 is referenced by page (1:476007), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:490950) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:490950). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:490950), slot 0, text ID 985333760 is referenced by page (1:490930), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:493604) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:493604). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:493604), slot 0, text ID 613679104 is referenced by page (1:493593), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:496267) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:496267). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:496267), slot 0, text ID 179961856 is referenced by page (1:495862), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:546836) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:546836). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:546836), slot 0, text ID 225050624 is referenced by page (1:546598), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:554558) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:554558). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:554558), slot 0, text ID 2686976 is referenced by page (1:554481), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:571110) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:571110). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:571110), slot 0, text ID 1405157376 is referenced by page (1:570190), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data): Page (1:571405) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data), page (1:571405). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057594124828672 (type LOB data). The off-row data node at page (1:571405), slot 0, text ID 1107755008 is referenced by page (1:571251), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2714:21)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2715:2)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2716:8)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 523304960 owned by data record identified by RID = (1:2737:10)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 985333760 owned by data record identified by RID = (1:2737:26)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 179961856 owned by data record identified by RID = (1:2740:17)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2748:16)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2757:11)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2761:9)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:2793:9)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 613679104 owned by data record identified by RID = (1:2840:0)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:4339:16)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:4342:5)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:107627:2)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:107627:17)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:107627:25)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 225050624 owned by data record identified by RID = (1:107627:28)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 1045168128 owned by data record identified by RID = (1:110237:4)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:110318:9)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 1405157376 owned by data record identified by RID = (1:110780:10)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:526312:12)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:526315:34)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:526317:3)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 2686976 owned by data record identified by RID = (1:526318:6)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 225050624 owned by data record identified by RID = (1:544144:4)
Msg 8929, Level 16, State 1, Line 1
Object ID 1945773989, index ID 1, partition ID 72057598217420800, alloc unit ID 72057597161635840 (type In-row data): Errors found in off-row data with ID 1107755008 owned by data record identified by RID = (1:544147:5)
CHECKDB found 0 allocation errors and 53 consistency errors in table 'SGM_FileStore' (object ID 1945773989).
CHECKDB found 0 allocation errors and 57 consistency errors in database 'TESTDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TESTDB).
August 6, 2014 at 12:06 am
Markus (8/5/2014)
I backed up a production database from Data Center A, backed it up across the network and restored it to a SQL Server in Data Center B which is nonproduction. THe restore worked fine. However, a DBCC CHECKDB shows the below errors. If I run a DBCC CHECKDB on the DB in DataCenter A it says zero errors. The SQL Server in DataCenter A is SQL2008R2 SP1 and the one in DataCenter B is SQL2008R2 SP2. My question is that my Storage guys say there is no issues with the back end storage. I say that cannot be... He thinks we need to rule out the SQL Server binaries as the issue. I say this is data corruption. Thoughts?
That's data corruption. It may have happened during the restore, it may have happened during the copy. Try backing up WITH CHECKSUM and doing a verify on the backup before restoring it
I know that is a very minor checkdb routine that is run upon a db restore.
There is not. CheckDB runs only when it is explicitly run by someone or some job. There is never an automatic checkDB ever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 5:04 am
Hi
So it is the clustered index affected. If it was me I'd probably try DBCC PAGE ('myDB', 1, 952663, 2); on both the restored backup and the broken DB first to see and compare in Winmerge to see if I could infer anything from that (e.g. if both very similar but just some corruption in the page header then you could use DBCC with style 3 on the restored DB to get a good idea of specific rows to restore after REPAIR_ALLOW_DATA_LOSS. Otherwise you'll just need to do a data compare between the two tables and try and see if you can figure out rows that need restoring that way.
August 6, 2014 at 5:13 am
I just opened a case with Microsoft. Something very bizzare is going on. On the production SQL Server I ran the database integrity check job and one of the other databases showed 4 errors and creates a SQLDump file. I ran the DBCC CHECKDB against that same database again and it shows ZERO errors. Now how could THAT be???????
August 6, 2014 at 5:22 am
GilaMonster (8/6/2014)
Markus (8/5/2014)
I know that is a very minor checkdb routine that is run upon a db restore.There is not. CheckDB runs only when it is explicitly run by someone or some job. There is never an automatic checkDB ever.
Are you sure about that Gail?
Below is an extract from a SQL Server log at the time a database was recovered from a Restoring state. I can assure you that the CHECKDB shown was not run by anyone or a scheduled job.
07/31/2014 22:04:40,Backup,Unknown,Restore is complete on database 'BrightTalk'. The database is now available.
07/31/2014 22:04:40,spid54,Unknown,CHECKDB for database 'BrightTalk' finished without errors on 2012-06-25 01:00:04.770 (local time). This is an informational message only; no user action is required.
07/31/2014 22:04:40,spid54,Unknown,Recovery is writing a checkpoint in database 'BrightTalk' (5). This is an informational message only. No user action is required.
07/31/2014 22:04:40,spid54,Unknown,Starting up database 'BrightTalk'.
Regards
Lempster
August 6, 2014 at 5:28 am
Gail. I ran another backup with the with checksum and it ran good. I ran a verify of the backup, good. I restored it.. good. I ran a checkdb, no errors.
Something very odd is going on.... Case opened with MSFT.
August 6, 2014 at 5:36 am
Lempster (8/6/2014)
Are you sure about that Gail?
Yes, absolutely, 100% certain
Below is an extract from a SQL Server log at the time a database was recovered from a Restoring state. I can assure you that the CHECKDB shown was not run by anyone or a scheduled job.
And I can assure you that checkDB wasn't run at all...
07/31/2014 22:04:40,spid54,
Unknown,CHECKDB for database 'BrightTalk' finished without errors on 2012-06-25 01:00:04.770 (local time).
In fact, that database has not had a consistency check run on it in over 2 years. I do hope it's a dev DB...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2014 at 5:59 am
Ah, yes, I didn't spot that other timestamp, apologies.
Yes, it's an old database used for testing.
So, when a database is restored/recovered, the last time that a CHECKDB was run is written to the SQL Server log? I didn't know that so I I learned something today!
Regards
Lempster
August 6, 2014 at 6:01 am
Last time CheckDB ran *successfully*
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2014 at 7:43 am
I opened a case with Microsoft Support. It all points to hardware, IO stack, storage.. something like that. However, server team and storage team so no issues. Microsoft said it will take 2-3 days to digest all of the info and get back to me. Meanwhile we are to have a session here to try and eliminate pieces and parts.
August 15, 2014 at 9:18 am
Update:
Well, Microsoft was really of no help. Call the vendor and they had the server team update the firmware for the node and all appears to be running fine now. Very strange.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply