Microsoft SQL Server is a well-known Relational Database Management System and due to its effectiveness, it commonly used for creation as well as maintenance of a huge database. SQL Server allow users to store and manage a large amount of data. SQL Server database file comprises of several pages to store its allocation structure along with Global Allocation Page. Entire information related to the extent is allocated in the file of SQL Server that contained in IAM (Index Allocation Map) pages. It contains the extent information about indexes and tables.

Problem Statement

Sometimes, because the presence of many IAM pages for a single point, a user might experience several errors. Such types of error indicating SQL server table corruption. Due to this corruption, SQL user will get an error 8947:

Reasons Behind SQL Server Error 8947?

When a user faces error code 8947 it means that an IAM page must have one-bit extent in GAM of SQL Server in a proper state and it will help to display which extent is allocated to which specific index. Now, due to unfavorable condition these two IAM pages like P_ID1 and P_ID2 contain the common GAM interval. Apart from this, in some situations, SQL error 8947 appears on multiple IAM pages within the server. Hence, the main cause of this error is that the IAM chain for particular index those posses 2 IAM pages which cover up the same GAM interval. Generally, the common cause of this problem is the failures of hardware. Thus, in that case, a user needs to recover the corrupted SQL database.

Understanding some terms to get the reason why this Error code 8947 take place

It is necessary to have knowledge related to Global Index Map (GAM), Index Allocation Map (IAM), indexes, interval and page in SQL server.

• IAM pages: It helps to store the detail related to the extent use through tables or indexes as per unit allocation.
• GAM Pages: It states either extent are allocated or not. Whatever has being allocated to any extent it will get recorded in these pages.
• Page: It is a primary unit to save or store data and its 8KB size. The disk space is divided into MDF and NDF files logically.
• Extent: It is a storehouse where continuous eight pages are physically used to manage the several pages in an effective style.
• Interval: It can be represented as the availability of total space in a file that helps to map a GAM page and its amount can be 4 GB approximately in size.
• Index: Every index has multiple extents that are allocated from GAM interval and make sure have an IAM page for particular GAM interval.

Manual Method to Fix SQL Server Error Code 8947

The following manual solutions required to overcome this SQL server error message:

1. Hardware Diagnostic and Repair

One of the major causes of corruption in a database table can be the hardware issue. The MS Windows NT system, application logs, and SQL server error log must be examined. Thus, if the hardware corruption issue raised then, the particular operation should be accordingly performed. As per users need, any particular corrupted component or complete hardware must be changed.

In case, if required then the disk drives might be reformatted and operating system must be installed again. Moreover, it should be assured that the server machine doesn’t have to enable caching on disk controller. This solution will become helpful if the issue happened in hardware.

2. Restore Backup

If the hardware repair is unable to resolve an issue, then users must use the different solution to eliminate SQL Server error 8947. Hence, if a clean and healthy backup is available then, restore SQL database from backup file for regaining the accessibility of the data items which is stored in SQL server database. It can be possible, only if the backup is available.

3. DBCC CHECKDB Execution

It is an existing utility that can be executed to restore or repair the SQL server database file. Still, in the beginning, it can be run without any need for repair clause thus, to verify the reason and damage level. It helps to run an appropriate restore clause to fix an error. The main reason is that DBCC CHECKDB helps to rebuilt the IAM chain which holds P_ID2 during detection of damaged IAM pages. It can fix other errors but the problem is that it justifies the data loss which is not suitable.

Alternate Solution to Resolve SQL Server Database Error 8947

The most suitable solution for fixing this error code 8947 is SQL Recovery. It is safe and secure style to troubleshoot this error with the help of following steps:

Step1: First, launch SQL Recovery, add MDF file and then, press Open button

Step 2: Once the files are added, the application will provide two options to scan SQL file i.e. Quick & Advance. After scanning, the application will Preview the completed recovered data of MDF and NDF files including tables, stored procedures, triggers, and views, etc.

Step 3: Now, select an option among two i.e., SQL Server Database & SQL Server Compatible Scripts for exporting the database and click on save option

Finally, a user will be able to get MDF or NDF file without any error with help of this solution.

Conclusion

This article describes the server error, which creates obstacle while accessing the SQL Server database. Also, we have discussed a best possible solution which helps to overcome this issue. Initially, it is recommended to restore the backup file of the database, if a user is already having it. Else, a user can go for another solution for resolving SQL server error 8947.