April 20, 2017 at 7:51 am
Hello all,
I recently restored a backup on a new SQL Server. Everything is fine except when I try to run a query, I get the following error:
"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x57856034; actual; 0x1108d49a)"
Can I please get some guidance on how to fix this issue? Thanks in advanced!
April 20, 2017 at 8:37 am
Pretty sure that means your DB is corrupt. I'd run DBCC CHECKDB
and see if it is bad. If so, I'd run it on your live one that you got the backup from as that one may be corrupt as well. If LIVE isn't corrupt, I'd re-run your backup and try restoring from that.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 20, 2017 at 8:55 am
Please run the following and post the full and complete output. Run it on both that database and on the source database that the backup came from
DBCC CheckDB WITH NO_INFOMSGS
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
April 20, 2017 at 10:24 am
Hello bmg002 and Gilamonster,
Thank you for your help. I rant the following query:
DBCC CHECKDB ('ACSA') WITH NO_INFOMSGS
and got the following:
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -65536 (type Unknown), page (65535:-1). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -10.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 418100530, index ID 1, partition ID 72057594046251008, alloc unit ID 72057594051035136 (type In-row data), page (1:6541). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 418100530, index ID 1, partition ID 72057594046251008, alloc unit ID 72057594051035136 (type In-row data): Page (1:6541) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 418100530, index ID 1, partition ID 72057594046251008, alloc unit ID 72057594051035136 (type In-row data). Page (1:6541) was not seen in the scan although its parent (1:6784) and previous (1:6540) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 418100530, index ID 1, partition ID 72057594046251008, alloc unit ID 72057594051035136 (type In-row data). Page (1:6542) is missing a reference from previous page (1:6541). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 418100530, index ID 2, partition ID 72057594046316544, alloc unit ID 72057594051100672 (type In-row data): Page (1:48425) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 418100530, index ID 2, partition ID 72057594046316544, alloc unit ID 72057594051100672 (type In-row data). Page (1:48425) was not seen in the scan although its parent (1:48428) and previous (1:48424) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 418100530, index ID 2, partition ID 72057594046316544, alloc unit ID 72057594051100672 (type In-row data). Page (1:48426) is missing a reference from previous page (1:48425). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 7 consistency errors in table 'Events' (object ID 418100530).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'ACSA'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ACSA).
Should i run the last suggestion: "Repair_allow_data_loss"?
April 20, 2017 at 10:33 am
Is that on the restored backup, or on the source database that the backup came from?
Should i run the last suggestion: "Repair_allow_data_loss"?
Do you want to lose an unknown amount of data?
And that's not a suggestion at the end, just an observation as to how severe the problem is.
I hope that database name doesn't stand for what I think it does...
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
April 20, 2017 at 10:58 am
Great, i will do that and see what information (if any) is lost. Even if some is lost, its from a table that contains no valuable information, thanks for the help!
April 20, 2017 at 11:19 am
NO!
It is NOT a suggestion! It's not the recommended approach.
Asking again,:
Is that on the restored backup, or on the source database that the backup came from?
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
April 20, 2017 at 11:21 am
Just because the "Events" table has information you don't care about, there was also 8 consistency errors in database 'ACSA'.
Running that could drop things you DO care about. It could be a trigger, a PK, a FK, an index, statistics. I imagine it could even be tables or service broker stuff or users or roles... you don't know what will be lost.
I would try running it on a TEST version of the system first as if data is lost, you don't want to panic if you find out everything is gone.
Also, do you get similar errors when you run that against the system the backup was created from?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 20, 2017 at 12:04 pm
bmg002 - Thursday, April 20, 2017 11:21 AMthere was also 8 consistency errors in database 'ACSA'.
Not 'also'
There are 8 errors in the ACSA database, 7 of them are in the Events table and one is not associated with any object.
You can't lose object definitions with repair, it won't deallocate pages from system tables. If system tables are damaged you've just got irreparable corruption.
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
April 20, 2017 at 12:09 pm
GilaMonster - Thursday, April 20, 2017 12:04 PMbmg002 - Thursday, April 20, 2017 11:21 AMthere was also 8 consistency errors in database 'ACSA'.
Not 'also'
There are 8 errors in the ACSA database, 7 of them are in the Events table and one is not associated with any object.
You can't lose object definitions with repair, it won't deallocate pages from system tables. If system tables are damaged you've just got irreparable corruption.
Oh, sorry about that. I thought that there were 7 consistency errors in the table, and 8 in the database. I misunderstood the result.
And I thought that when it went to fix the database inconsistencies, it could lose database level stuff.
Learn something new all the time. Thanks for the info :).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 20, 2017 at 12:14 pm
This is not on a LIVE database. It is a copy I have on my SQL machine.
I ran the following script:DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS)
Only 7 rows are missing and Eeverything is functional so far.
Thank you for your help and patience, as you can tell I am fairly new to SQL but hope to become a GURU one day 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply