In MS SQL Server database, a PFS (Page Free Space) page has one byte for each of the pages existing in the file interval it maps. This byte contains a bit that indicates that the associated page is a mixed page. An IAM page includes a mixed page array, which is an array of eight page IDs, to keep the mixed pages that have been allocated to the index. In some cases, when the database goes corrupted, the page containing 'mixed page' bit is not recognized correctly. Thus, you can detect such specific cases of database corruption and, take corrective measures for
MS SQL Repair.
Most of the SQL database corruption problems can be analyzed and detected by running DBCC CHECKDB command. Let's consider the below error message, which typically appears when the discussed problem occurs. When you run DBCC CHECKDB command to check the integrity of database, following error might pop up:
Server: Msg 8906, Level 16, State 1, Line 0
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
Error 8906 is most likely to occur when a page with 'mixed page' bit set up in PFS byte has not been detected as an IAM page or in a mixed page array of an IAM page, which indicates database corruption.
Solution
If you encounter error 8906 after running DBCC CHECKDB on your SQL Server database, apply the below measures to solve it:
Run hardware diagnostics to isolate hardware-related problems. Corrupted hardware components are known to cause these issues if database corruption persists.
Restore the database from last good backup available.
If no clean backup is available, run DBCC CHECKDB without any repair clause to know the extent of damage and required
MDF Repair clause. Again run DBCC CHECKDB command with the recommended repair clause. Make sure to backup your database before executing the repair command, in order to avoid post-crash results.
Use a third-party MS SQL Repair utility, in case DBCC CHECKDB doesn't work.
For best results, it is recommended to use Stellar Phoenix SQL Recovery software. Designed to run powerful and safe scanning algorithms, this SQL Repair utility can restore all database objects in a simple way. The application supports MS SQL Server 2008, 2005, and 2000.
