How to Fix Multiple IAM Pages Error 8947?

Fix Multiple IAM Pages Error 8947

Summary: This post discusses the ‘Multiple IAM Pages Error 8947’ that SQL users encounter on running the DBCC CHECKDB command. It also explains the methods to troubleshoot the error manually or by using a third-party tool.

When running DBCC CHECKDB on a SQL database, you may come across the ‘Multiple IAM Pages Error 8947.’ The complete error message resembles the following:

Msg 8947, Level 16, State 1, Line 1
Table error: Multiple IAM pages for object ID 1803153592, index ID 1, partition ID 72057594056081208, alloc unit ID 72057594061979608 (type In-row data) contain allocations for the same interval. IAM pages (1:22250) and (1:28253).

CHECKDB found 1 allocation errors and 0 consistency errors in table 'tblname' (object ID 1803153469).

CHECKDB found 1 allocation errors and 0 consistency errors in database 'database_name'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (database_name).

The error says that there is a table error. Consistency errors are found in a table with index ID 1. This means there is corruption in the clustered index on a table.

Methods to Fix Multiple IAM Pages Error 8947

Before using the methods to fix the error 8947, ensure that SQL Server Management Studio (SSMS) is installed on your system.

Method 1 – Rebuild the Clustered Index on the Table

To fix multiple IAM Pages Error 8947, you must first try to repair the corrupted index. To do so, rebuild the table index by executing the following T-SQL query in SSMS:

Note: If you’ve multiple files in your SQL database, rebuilding might not work. A better alternative would be to drop and recreate the index.

ALTER INDEX tblname ON dbo.tblname REBUILD WITH (ONLINE = OFF)

after that DBCC CHECKDB (database_name) ran with no errors.

If rebuilding or dropping and recreating the index doesn’t fix the 8947 error, restore the database file containing the corrupt table from a healthy backup copy.

If there is no backup, proceed with the following solution.

Method 2 – Run DBBC CHECKDB with Repair Option

Run DBCC CHECKDB with the recommended minimum repair option ‘REPAIR_ALLOW_DATA_LOSS’ to repair corruption in the database. For this, run the following script:

ALTER DATABASE [database_name] SET EMERGENCY

ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC DATABASE ([database_name], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [database_name] SET MULTI_USER

In this script, we are putting the database in EMERGENCY mode. Doing so will allow you to repair an inaccessible database. Next, the database is set to SINGLE_USER mode to prevent other users from making any changes to the DB during the repair process. And then, the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command is run to repair the database. Once the DB is repaired, its state is changed back to MULTI_USER mode.

While this might work, using ‘REPAIR_ALLOW_DATA_LOSS’ option can result in data loss. A better alternative is to use a SQL repair tool that fixes a corrupted database file and restores all its data.

Method 3 – Using a SQL Repair Tool

Use a third-party SQL repair tool to fix multiple IAM Pages Error 8947. The tool helps resolve the error by repairing the database file (.mdf) containing corrupted indexes while maintaining data integrity. A SQL repair tool can also help restore all other database objects like tables, deleted records, triggers, stored procedures, etc.

Fix Multiple IAM Pages Error 8947

Conclusion

Sometimes, running the DBCC CHECKDB commands against a database may return an ‘IAM Pages Error 8947’. The error occurs due to clustered index corruption. You can resolve it by rebuilding the table with corrupted indexes or dropping and recreating those indexes. If this doesn’t help, try restoring the database from a recent backup. If the backup is unavailable, run DBCC CHECKDB with the recommended repair option or use a SQL database repair tool to resolve the problem.

fix multiple IAM Pages Error 8947