Dealing With Multiple Iam Pages Error In Sql Server

By Author: merge tool
Total Articles: 35
Comment this article

Many of us deal with MS SQL Server quite often and its errors in SQL Server .MDF files too. Multiple IAM Pages error is one such error in SQL Server database that leads to inaccessibility of .mdf file and halts work flow. You must be wondering what this Multiple IAM Pages error is.
Well, it goes this way. SQL Server file is consisted of different pages that store its allocation structures. GAM (Global Allocation Page) is the one that contains information related to allocated extents in this file. Whereas, the allocation pages that contain information of the extents that an index or table uses are termed as Index Allocation Map (IAM) pages. Sometimes, you get errors that it has found multiple IAM pages for a single object. Such errors usually indicate table corruption that needs to be repaired using advanced and professional SQL Server data recovery software.
You might encounter the following error with your SQL Server database table:
Server: Msg 8947, Level 16, State 1
Table error: Multiple IAM pages for object ID O_ID, index ID I_ID contain allocations for the same interval. IAM pages P_ID1 and P_ID2.
...
... Reasons behind this issue
The error message suggests that the IAM chain for the specified index has minimum of 2 IAM pages (referred as P_ID1 and P_ID) and cover the same GAM interval. By interval, it means the file space that GAM page uses to map, which is approximately calculated as 4 GB. Each index having more than one extent that is allocated from a GAM interval is required to have an IAM page for that particular GAM interval. For all extents of GAM interval, an IAM page contains one bit for each extent. A set bit indicates that the particular index is allocated to that index.
Otherwise, the common reason for this behavior is due to hardware failure.
Resolution
You need to perform these methods to recover from the situation:
Ensure that no hardware failure issue exists. Run hardware diagnostics and check application, Windows and SQL Server error log to know the exact issue. Replace the damaged hardware, if needed.
If clean backup is available, restore from the database from it.
If no valid backup is present, run DBCC CHECKDB command without using any repair clause to know the extent of corruption and repair clause required to be applied. Execute DBCC CHECKDB with the suggested repair clause.
In case above measures fail, don't lose heart as you can scan the database using advanced SQL Server data recovery tools. These are powerful and aware winning SQL database repair applications. SQL Server Data Recovery tool is efficient in recovering data from corrupt and damaged .mdf files created with MS SQL Server 2000 and MS SQL Server 2005.