A SQL Server file is consisted of different pages that store its allocation structures. The Global Allocation Page (GAM) 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 or restored from backup. In absence of clean backup, you should use MDF Repair tools.
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.
Cause
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.
One of the common reasons for this behavior to occur is hardware failure.
Solution
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
. If above measures fail, scan the database using advanced mdf file repair tools. These are powerful SQL database repair applications designed to offer safe and effective MDF Repair.
Stellar Phoenix SQL Recovery is a premier tool to repair damaged SQL databases. It supports SQL Server 2008, 2005 and 2000. With self-descriptive interface, advanced repair options and read-only design, it is an advanced repair mdf tool to repair all database objects. The software is compatible with Windows Vista, XP, 2003, 2000 and NT.