Exploring and Rectifying SQL Error 8906
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
Message Text
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.
Additional Information
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.
Factors Responsible for SQL Server Error 8906
The reasons that cause the occurrence of SQL Error 8906 are listed here:
- Hardware Breakdown: Inappropriate functionality of hardware may be responsible for existence of above mentioned error. The hardware problem can be detected by running hardware diagnostics. The detected issue (if any) can be resolved afterwards.
- Software Malfunctioning: The error may have occurred due to abnormal working of any software application. Sometimes SQL errors take place as a result of confliction between the applications installed on machine.
Work Around for Resolving SQL Error 8906
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.
Alternate Solution
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.
