January 23, 2018 at 9:59 am
Date and time: 2018-01-23 17:47:18
Dear all,
While doing DBCC CheckDB I found this issue below:
Command: DBCC CHECKDB ([dwhStaging]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 8947, Level 16, State 1, Line 1
Table error: Multiple IAM pages for object ID 1803153469, index ID 1, partition ID 72057594056081408, alloc unit ID 72057594061979648 (type In-row data) contain allocations for the same interval. IAM pages (1:22260) and (1:28263).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'MDS.ExternalKeyMapping' (object ID 1803153469).
CHECKDB found 1 allocation errors and 0 consistency errors in database 'dwhStaging'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dwhStaging).
Outcome: Failed
Duration: 00:02:54
Date and time: 2018-01-23 17:50:12
Is it an index that is corrupted? how can I see exactly what is happening and solve it?
Thank you
January 23, 2018 at 10:12 am
That's a table that's corrupt.
In this case, I suspect that a repair with allow_data_loss will work and will not lose data (there are no row that are unreadable, just some weirdness with the allocation structure)
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
January 23, 2018 at 11:27 am
GilaMonster - Tuesday, January 23, 2018 10:12 AMThat's a table that's corrupt.In this case, I suspect that a repair with allow_data_loss will work and will not lose data (there are no row that are unreadable, just some weirdness with the allocation structure)
Thank you for your feedback. I Will teu the allow data loss. Why do you think this happened? What can cause this situation?
January 23, 2018 at 11:48 am
Corruption is usually related to problems with the IO subsystem.
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
January 23, 2018 at 12:16 pm
GilaMonster - Tuesday, January 23, 2018 11:48 AMCorruption is usually related to problems with the IO subsystem.
And those usually related to not keeping EVERYTHING in the ENTIRE IO stack up to date (including ALL firmware and drivers, even down to the individual disk/NIC/switch/etc).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2018 at 12:46 am
Hello,
After doind the repair with allow data loss I am getting this:
Repair: IAM chain for object ID 1803153469, index ID 1, partition ID 72057594056081408, alloc unit ID 72057594061979648 (type In-row data), has been truncated before page (1:22260) and will be rebuilt.
Msg 8947, Level 16, State 1, Line 11
Table error: Multiple IAM pages for object ID 1803153469, index ID 1, partition ID 72057594056081408, alloc unit ID 72057594061979648 (type In-row data) contain allocations for the same interval. IAM pages (1:22260) and (1:28263).
Could not repair this error.
Msg 8969, Level 16, State 2, Line 11
Table error: IAM chain linkage error: Object ID 1803153469, index ID 1, partition ID 72057594056081408, alloc unit ID 72057594061979648 (type In-row data). The next page for IAM page (1:28263) is (1:22260), but the previous link for page (1:22260) is (0:0).
There are 0 rows in 248 pages for object "MDS.ExternalKeyMapping".
CHECKDB found 1 allocation errors and 1 consistency errors in table 'MDS.ExternalKeyMapping' (object ID 1803153469).
DBC
Probably the best thing to do is drop indexes and recreate them again, correct? This seems to be an error with an index?
January 24, 2018 at 5:36 am
No, that's the table (index 1 is the clustered index)
You can try dropping the clustered index, but do it on a copy of the DB, not the production DB first.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply