October 16, 2013 at 10:04 am
So after hitting a Fatal Error 823 in my tempdb.mdf I went through and ran a DBCC CHECKDB on all my databases. All but 1 was clean and DBCC CHECKDB generated the following error:
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:330) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057594044612608 (type Unknown), but it was not detected in the scan.
Showing all messages is get the error after all the Service Broker Msg's and before any of the database objects.
Seeing this i went back to my oldest backup and unfortunately the corruption is there so I have no good backups. Good news is that the app is chugging along just fine and I'm not panicking.
Since CHECKDB gave me the status that "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB" I am taking things very slowly (first time dealing with any type of corruption). On a backup I went ahead and ran the repair_allow_data_loss and it fixed the issue but before I attack production I want to be sure exactly what it is fixing.
What is perplexing me on the error message is that object ID 0 and index ID -1 make no sense as the objects do not exist (unless they are hidden internal objects?)
After reading http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/ I used DBCC PAGE to get the page and the only thing different from the IAM page Paul pulled and mine in the header would be expected (different Metadata however the PartitionID of 0 again does not exist.)
After looking at the page im still a little lost but some one with more experience may be able to make something of it.
I have included the entire page bellow
PAGE HEADER:
Page @0x00000007B0A4C000
m_pageId = (1:330) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 102 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044612608 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 90 m_slotCnt = 2
m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0
m_lsn = (809:1098:42) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 703344359
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
IAM: Header @0x000000002007A064 Slot 0, Offset 96
sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (1:0)
IAM: Single Page Allocations @0x000000002007A08E
Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)
IAM: Extent Alloc Status Slot 1 @0x000000002007A0C2
(1:0) - (1:6392) = NOT ALLOCATED
I also posted this to dba.stackexchange.com but have not had much luck there. http://dba.stackexchange.com/questions/51562/how-can-sql-server-have-an-index-allocation-map-error-on-object-id-0-index-id
EDIT: Removed EDIT with incorrect conclusion
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 17, 2013 at 9:03 am
Well after much research and digging deeper into the storage internals than I ever have before, I am reasonably sure this IAM page does not track any data pages which means that I can safely fix the issue with out any data loss (woot).
That does not solve the underlying issue of how this may have occurred which while interesting is a secondary issue right now.
It is always fun to answer your own questions.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 17, 2013 at 9:12 am
CapnHector (10/16/2013)
What is perplexing me on the error message is that object ID 0 and index ID -1 make no sense as the objects do not exist (unless they are hidden internal objects?)
The page has been zeroed out, probably by the IO subsystem. It's just a page full of 0000000000.
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
October 17, 2013 at 9:17 am
GilaMonster (10/17/2013)
CapnHector (10/16/2013)
What is perplexing me on the error message is that object ID 0 and index ID -1 make no sense as the objects do not exist (unless they are hidden internal objects?)The page has been zeroed out, probably by the IO subsystem. It's just a page full of 0000000000.
Thank you for the explanation. I will let the storage team take it from there then after i fix it and get a clean backup.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 17, 2013 at 12:21 pm
CapnHector (10/16/2013)
EDIT: After looking at it some more it really looks like this IAM page is not tracking any allocated space since there are no single page allocations and pages (1:0) - (1:6392) are not allocated..
You can't conclude that. If the page has been zeroed out, anything that was on the page has been replaced by zeros and anything that the page says can not be trusted.
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
October 18, 2013 at 1:33 pm
GilaMonster (10/17/2013)
CapnHector (10/16/2013)
EDIT: After looking at it some more it really looks like this IAM page is not tracking any allocated space since there are no single page allocations and pages (1:0) - (1:6392) are not allocated..
You can't conclude that. If the page has been zeroed out, anything that was on the page has been replaced by zeros and anything that the page says can not be trusted.
well that stinks. Guess we will never know if we lost data or not (probably did though). I also edited my previous posts to remove those bits about being an IAM page.
Thanks for correcting my mistakes. (learning hurts some times when you bang your head on your desk.)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 18, 2013 at 1:41 pm
If the only damage was the IAM page, probably not. They can be recreated without losing data, though it does require the allow_data_loss option.
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