{"id":165276,"date":"2024-04-17T11:37:48","date_gmt":"2024-04-17T11:37:48","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=165276"},"modified":"2024-05-30T04:36:52","modified_gmt":"2024-05-30T04:36:52","slug":"repair-database-corruption-gam-sgam-and-iam-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/repair-database-corruption-gam-sgam-and-iam-in-sql-server\/","title":{"rendered":"Repair Database Corruption: GAM, SGAM, and IAM in SQL Server"},"content":{"rendered":"

In SQL Server, a page is the fundamental unit for storing data and extents are the basic unit that help in efficiently managing the pages. An extent is a combination of eight physically connecting pages. In SQL Server, there are two types of allocation maps used for recording the extent of allocation – Global Allocation MAP (GAM) and Shared Global Allocation MAP (SGAM). There is another page, called Index Allocation Map (IAM) that maps the extents in the 4-GB part of the SQL Server database file utilized by an allocation unit. Sometimes, these SQL database pages get corrupted or damaged due to various reasons, requiring repair of database corruption. In this post, we will see how to diagnose and repair SQL database corruption<\/a> – GAM, SGAM, and IAM page corruption issues in SQL Server.  <\/p>

What are the GAM, SGAM, and IAM Pages?<\/strong><\/h2>

Global Allocation Pages (GAM) are responsible for handling allocation extents, inside the database. They help in identifying free extents when required.<\/p>

Shared Allocation Map (SGAM) pages are like granular maps. They handle the allocation of extents with specific file groups in the database.<\/p>

Index Allocation Map (IAM) pages are special internal pages that act like a registry for data storage. They track the data page’s location in the database file.<\/p>

How to Diagnose GAM, SGAM, and IAM Page Errors?<\/strong><\/h2>

When you run queries, you may face an error message similar to this one:<\/p>

Msg 8905, Level 16, State 1, Line 1\n\nExtent (5678:1234) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.<\/code><\/pre>
This error indicates that the GAM page is corrupt.<\/p>
Another way to detect errors in database pages is by using the DBCC CHECKDB co<\/a>mmand<\/a> (see the below example).<\/p>
DBCC CHECKDB (stellardb)\n\nGO<\/code><\/pre>
There is an undocumented command, named dbcc PAGE. This is an internal command used by Microsoft. However, you can use it to diagnose the database pages. Here’s the code to use this command.<\/p>
DBCC PAGE (stellardb, 1, 354, WITH NOHEADER)\n\nGO<\/code><\/pre>
Here, stellardb is the database name, 1 is the file number, and 354 is the page number.<\/p>
How to Fix Corruption in SQL Database GAM, SGAM, and IAM Pages?<\/strong><\/h2>
Here are some solutions to fix the GAM, SGAM, and IAM page errors, and corruption in the SQL database.<\/p>
Restore Database from Backup<\/strong><\/h3>
If you have a backup, you can use it to restore your data. Here are the steps to restore database backup using SQL Server Management Studio (SSMS):<\/p>