Read time 5 minutes
We are here with a blog on another SQL Server error. Our intent in this blog is to help you understand the SQL Server error ‘multiple IAM pages error 8947,’ its occurrence, causes, and effects on SQL Server working. And finally we provide you some possible ways to fix it.
The SQL Server error “Multiple IAM pages error 8947” can occur suddenly while you are working with the SQL Server or trying to access the SQL Server database. And obviously the occurrence of this error would affect the further functioning of the SQL Server.
The exact error message which the SQL Server encounters may look like-
Server: Msg 8947, Level 16, State 1
Table error: Multiple IAM pages for object ID O_ID, index ID I_ID containing allocations for same interval. IAM pages P_ID1 and P_ID2.
Explaining the error message:
By reading the above error message, any SQL Server user can conclude that there is some issue with the SQL table, and it is corrupted or crashed due to certain event. Moving on, we read that there are multiple IAM pages P_ID1 and P_ID2 for the object and index.
How does the “multiple IAM pages error 8947” occur?
Any SQL Server database has multiple pages to hold and store the allocation structures. Out of these pages, there are two crucial pages known as Global Allocation Page (GAP) and Index Allocation MAP Page (IAM). The GAP page contains complete information about the allocated extents in the SQL Server database while the IAM page records information about all the extents used by the table or index. An IAM page stores a single bit/extent within every GAP page. This single bit is used to identify the extent allocated to a definite index.
Sometimes this regular IAM chain or sequence for an index is broken with the introduction of two IAM pages (as mentioned in the error) for a GAP interval. So, having multiple IAM pages (more than one) for
Reasons behind the error
Now that we know about the nature of the error and the whole process of its occurrence, can we state the reason for these multiple IAM pages? No, we can only assume the reasons which can be either hardware issues or corruption in the SQL Server database. We can try to fix this error up by troubleshooting these causes.
Methods to fix corrupt SQL database (multiple IAM pages error 8947)
A. Fix Hardware Issues
SQL Server users can detect the hardware failures by running the hardware diagnostic tools, checking SQL Server error logs, Windows NT system application log, etc.
After identifying the affected/damaged hardware component, replace it with the new, component. Or in severe cases, you can re-format the disk drive or install the operating system again.
B. Repair Corrupt SQL Database File
If you have performed the above method already and the error continues to occur, then there are almost definite chances of corruption in the SQL database file. To avoid repairing the whole database, you can practice the restore from a latest, healthy SQL backup file (if you have) using the Microsoft SQL Server Management Studio.
In the absence of a clean SQL Server database backup file, we suggest you to go for the only available option, i.e. repairing the SQL database file. You can repair your database file using the manual way by running DBCC CHECKDB query in the SQL Server Management Studio.
The detailed script to be run or execute for the clean database repair is:
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC CHECKDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
After executing this manual repair method, if you are able to fix the error then you would be quite fortunate because this repair method results in failure sometimes owing to the corruption in SQL database.
Use a third-party tool to recover SQL data
We recommend a professional tool to repair SQL database file as the final solution to fix “Multiple IAM pages error 8947”. Out of a lot of SQL database recovery tools, we can suggest you the most efficient and robust one, which is Kernel for SQL Database Recovery tool. The tool is highly advanced in terms of programming and designing, but integrates a user-friendly interface.
It can repair and recover corrupt/inaccessible SQL databases belonging to any SQL Server version easily. Using this utility, users can also recover deleted records in SQL Server which are removed accidentally or intentionally. The tool repairs even large databases and saves to live SQL server or a batch file. It offers a Preview which allows viewing SQL data before restoring it to the destination server. Its all features are remarkable and you can test it yourself using the free trial version of the tool.
