SQL Error 8947 – Overview

This page presents an overview of SQL server error 8947, causes that lead to this error and finally the resolution to fix the error. Sometimes SQL server does render a pop up with an error code 8947 when its database (stored as MDF and NDF file) is tried to access by any user or the database administrators. With the pop up of this error message, the accessibility to primary as well as the secondary (if present) files gets restricted which is certainly an undesirable situation. Therefore, by means of this informative page an attempt has been made to provide detail of why the error occurs and the way it can be fixed.

MS SQL Server Error Code 8947

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.

Expaination - To understand the error, it is indeed necessary to have knowledge about Global Index Map (GAM), Index Allocation Map (IAM), indexes, interval and page in SQL server.

• IAM Pages - It stores information about the extents used by table or the indexes per allocation unit.
• GAM Pages – It tells whether extents are allocated or not. Whatever has been allocated to the extents gets recorded into these page.
• Page – It is the fundamental unit to save data and its size is 8KB. The disk space owed to MDF or NDF file is divided logically into pages.
• Extent – It is known to be the storehouse of physically contiguous eight pages that are used to manage the pages in an effective manner.
• Interval – It can be described as the total space available in the file that a GAM page maps and it amounts to be approximately 4 GB in size.
• Index – Each index that has one or more extents allocated from a GAM interval must have an IAM page for that particular GAM interval.

Cause – An IAM page should have one bit per extent in the GAM of SQL server when in proper state and this shows which extent is allocated to which particular index. But sometimes due to any unfavorable condition the two IAM pages, P_ID1 and P_ID2 contain the same GAM interval. And in such situations SQL error 8947 appears in the server. Therefore, the root cause of this problem is that the IAM chain for a particular index owns two IAM pages that cover up the same GAM interval.

Action Required

• Hardware Diagnostic & Repair – One of the causes of corruption in the database table can certainly be a hardware issue. For confirmation; the MS Windows NT system, application logs and also the SQL server error log should be examined. Hence, if it is found that hardware is the issue then accordingly the operation should be performed. Either any particular damaged component or the complete hardware should be changed as per requirement.

If needed, the disk drives can be reformatted and also the operating system can be reinstalled. Moreover, it should be ensured that the server machine does not have write caching enabled on the disk controller. This solution proves to be successful if there is issue in the hardware.

• Backup Restore – If hardware repair does not resolve the issue, as the problem might not be related to it, any different method should be employed to eradicate the issue. Hence, if a healthy and clean backup of the server database is available, then the backup can be restored to regain accessibility to the data items stored in the SQL server database. But what if backup is not available? In such cases another methods as mentioned below, can obviously be applied to fix SQL error 8947.
• DBCC CHECKDB Execution – It is an in-built utility that can be executed to repair the SQL database file. However, at first it should be run without a repair clause to verify the level of damage caused. Thereafter, the command can be run with an appropriate repair clause to fix the error. The fact is that the command rebuilds the IAM chain holding P_ID2 while deleting the damaged IAM pages. It fixes other errors as well if present, but the problem is that it causes data loss which is generally undesirable.

Alternative Solution to Fix SQL Server Error 8947

When all the above methods fail, any external software application should be brought in use. Though there are multiple of them available in the market a professional, error-free tool should be utilized. A third party tool known a SQL MDF file recovery software is suggested here, the reason being that it is known to be a highly reliable software that can retrieve the damaged primary as well as secondary SQL database file and transfer it to the server without fail. Moreover, it has specialization in recovering the stored procedures, rules, triggers, functions and tables as well.