Title: Rectifying Error 8906 and repairing corrupt database in SQL Server 2000
Question: An IAM (Index Allocation Map) page consists of eight page Ids, collectively called mixed page array, and holds the pages that are allocated to the index. The IAM pages are used to optimize the tables by allocating and deallocating storage space. Sometimes the IAM page is not able to perform as expected.
Answer:
An IAM (Index Allocation Map) page consists of eight page Ids, collectively called mixed page array, and holds the pages that are allocated to the index. The IAM pages are used to optimize the tables by allocating and deallocating storage space. Sometimes the IAM page is not able to perform as expected. Such corruption instances can occur because of various reasons such as hardware malfunctions, virus infections, power outages, abruptly exiting SQL Server, etc. To resolve such situations, you should use appropriate corrective measures. In case, you are not able to recover SQL database then you should use a third-party SQL database repair software to do so.
Consider a scenario wherein you encounter the following error message while working on an SQL Server 2000 database:
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not allocated in any IAM. PFS flags 'PFS_FLAGS'.
Cause:
The root cause of this error is that a page that is not reflecting as an IAM page and also not seen in the mixed page array of IAM page. This is quite contradictory as this page has mixed page bit set in its PFS byte.
Resolution:
There could be the following resolutions that you can perform to resolve this error:
Look for hardware problems: You should run hardware diagnostics and check for problems, if any. You can also check the error log report to ascertain whether this error has occurred because of any hardware malfunctions. In addition, you should swap the hardware components to zero down on the cause of error. In the end, you can also consider reformatting the hard disks and reinstalling the operating system to get a fresh operating system.
Restore from backup: After confirming that this is not a hardware related error, you should check the backup of the database. If it is updated and clean, then you should restore the database from the backup.
Run DBCC CHECKDB: If none of the previous methods work, then you should run the DBCC CHECKDB command without any repair clause to know the level of corruption. Then, you should run it again with the recommended repair clause.
The aforementioned methods should repair the corrupted database. However, if you still encounter the same error again then you should use a third-party SQL database recovery software to do the needful. Such read-only tools perform SQL recovery without overwriting the existing database.