This article is contributed. See the original author and article here.
SQL Server Extents, PFS, GAM, SGAM and IAM and related corruptions-2
In previous post, we discussed the PFS, GAM and SGAM pages. Today, I’m going show you two related corruptions on these pages.
SQL Server checks the bit in PFS, GAM, SGAM and IAM are checked when new pages are allocated. It guarantees that it does not store data to a wrong page/extent.
These pages are crucial to SQL Server, SQL Server considers it as a corruption if they are messed up.
I’m going to use the same database I used in previous .
You may restore the backup file and give it a try. Please note, restoring database will consume some pages and change the PFS, GAM, SGAM and IAM. You may get a different view from my mine.
Let me show you some of the content of GAM and SGAM before the corruption samples.
Here are the GAM and SGAM page result:
1.GAM
2.SGAM
3.We can tell that the extent of page(1:352) and extents greater than that have not been allocated.
4.Let’s take a dive into the GAM page.
5.‘00000000 00f0’ interpretation:
5)Pages and extents of heaptable1
select allocated_page_file_id as [FileID],allocated_page_page_id as [PageID],page_type_desc,extent_page_id/8 as ExtentID, is_mixed_page_allocation,extent_page_id as [First Page in Extent],extent_page_id+7 as [LastPage in Extent],is_allocated From sys.dm_db_database_page_allocations(db_id(),object_id(‘dbo.heaptable1′),null,null,’detailed’) order by allocated_page_page_id
You will find all the data in this backup file dbtest20200823.zip.
1.Now let’s discuss the first corruption scenario: error 8903
1)The page 245,246,247,328,329,330,331,332,333 belongs to the table heaptable1.
2)Extent 30 and 41 have these pages.
3)These two extents are marked as allocated in GAM. What happen if the extents are marked as ‘not allocated’?
The answer is : that extent will be consider as corrupted and DBCC Checkdb may report the 8903 error:
Msg 8903, Level 16, State 1, Line 22
Extent (xx:xx) in database ID xx is allocated in both GAM (xx:xx) and SGAM (xx:xx).
4)It usually happens when disks or other hardware run into issue…
5)Here is the result of DBCC PAGE after the GAM page in data file is messed up:
6)’00000000 00f2’ interpretation.
7)DBCC PAGE of GAM with parameter 3.
8)Here is the result of DBCC CHECKDB:
9)Why (1:312) is marked as corruption?
Because I messed up the ’00000000 00f0ffff’: I replaced it with ‘00000000 00f2ffff‘.
The extent(1:328) is actually allocated, but ‘00000000 00f2ffff‘ conflicts the fact.
Takeaway: When SQL Server set a bit of extent to 0 in GAM to allocate an extent, it also set the bits in other allocation pages, like SGAM, IAM etc, that’s the normal behavior.
If the bits in these allocation pages conflict each other, SQL Server considers it’s corruption scenario.
Question: is the command ‘dbcc checkdb(dbtest,repair_rebuild)’ able to fix the issue? Why?
Please download the dbtest20200823_error8903.zip and give it a try.
2.Now let’s discuss the second corruption scenario: error 8905
1).The extent id of page(1:352) is 44, it’s not allocated yet. What if the extent 44(or the extent after 44) is marked as allocated only in GAM?
The answer is : that extent will be consider as corrupted and DBCC Checkdb may report the 8905 error:
Msg 8905, Level 16, State 1, Line 13
Extent (xxx:xxx) in database ID xx is marked allocated in the GAM, but no SGAM or IAM has allocated it.
2)It usually happens when disks or other hardware run into issue…
3)Here is the result of DBCC PAGE after the GAM page in data file is messed up:
4)’00000000 00f0ffff fe’ interpretation.
5)DBCC PAGE of GAM with parameter 3.
6)Here is the result of DBCC CHECKDB:
7)Why (1:512) is marked as corruption?
Because I messed up the ’00000000 00f0ffff ff’: I replaced it with ‘00000000 00f0ffff fe’.
Takeaway: When SQL Server set a bit of extent to 0 in GAM to allocate an extent, it also sets the bits in other allocation pages, like SGAM, IAM etc, that’s the normal behavior.
If the bit is only set in GAM, but not in SGAM or IAM, then there is erroneous info in the allocation pages, it’s a corruption! SQL Server detects it and raise an error.
Question: Is the command ‘dbcc checkdb(dbtest,repair_rebuild)’ able to fix the issue? Why?
Please download the dbtest20200823_error8905.bak and give it a try.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments