In this section we will discuss about the SQL Server error 8906, its occurrence along with the manual solution. The solution to rectify issues related to SQL database corruption is also discussed in later section. If such error occurs while working with SQL, the corresponding error message looks similar to this:
"Severity Level 16
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."
Explanation: The above stated message reflects on the screen whenever a page includes the "mixed page" bit placed in PFS byte, but this page is not visible either as IAM page or its merged page array.
PFS refers to 'Page Free Space'; it is second page in data file after file header which is considered as first page in data file. However the numbering is done as page no. 0 and page no. 1 for file header and PFS respectively.
IAM refers to 'Index Allocation Map' page that tracks around 4 GB space in a file. These 4GB portions are known as 'GAM intervals'.
PFS is used to keep record of page level allotment or allocation whereas SGAM tracks level allocation status. While locating the pages, the database engine identifies extent with empty pages by using SGAM and GAM.
The reasons that cause the occurrence of SQL Error 8906 are listed here:
Analyze Application Logs: Windows application logs and SQL error logs can be analyzed to detect any issue. Formatting system drive or reinstalling Operating System could help in resolving the issue.
Restore from Backup: Restoring the database through known clean backup helps in removing the error message.
Database Console Command: SQL Server error 8906 can be resolved through executing DBCC CHECKDB without repair clause to check the level of corruption. The command will recommend the appropriate repair clause and then the suggested repair clause can be executed to resolve the problem.
Note: It is recommended to create SQL database backup before executing DBCC CHECKDB for avoiding any afternaths as it may result in data loss.
However if the existing error message finishes with any sort of corruption in database tables, stored procedures, functions, rules, etc. or any of the data from MDF or NDF files gets missing, it can be restored back via third party application.
MDF recovery software is a effective utility that helps restoring data from corrupt MDF or NDF files without making any negotiations with their attributes. Advance scan mode helps in recovering deleted data from SQL files. The software helps in restoring recovered data into 'SQL Server database' as well as 'SQL Server Compatible scripts' (in case respective platform is not available). In addition, it helps in rescuing SQL database from SUSPECT mode and various error messages including SQL error 8906.