Understanding SQL Error 8905
To know about SQL Error 8905, it is required to understand the concepts of GAM, SGAM and IAM in brief. This error may result in further occurrence of instances of SQL error 8909. The error message that appears on screen seems similar to the following:
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:192) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it. ‘
I made another try, this time using DBCC DBREINDEX to rebuild indexes but could not get away with these errors.
Global Allocation Map (GAM): GAM pages tracks and records the information about allocation of extents. Whatever has been allocated to the extents gets recorded into these pages. Single GAM holds 64000 extents that comprises of equal to 4 GB data. It allocates 1 bit per extent; therefore, if 1 bit is allocated to the extent, it implies that the extent is empty. On the contrary if the bit count is 0, it can be concluded that the extent is been allocated.
Shared Global Allocation Map (SGAM): The purpose and main aim of the SGAM pages is to record the information about the extents. All relevant criteria such as the extents that are been utilized as mixed extents and holds unused page (at least one) is been recorded via SGAM pages. If 1 bit is been assigned; it denotes that the specified extent is been allocated as mixed extent with an unused page. For bit allocation as 0; it can be concluded that it is not a mixed extent. Alternatively, it also implies that the extent is a mixed extent with no unused pages.
Index Allocation Map (IAM): Whenever a database file is been utilized by an allocation unit; bits are allocated to the extents. The major function of the IAM page is to map the information about the extents that have been assigned to 4 GB partitions of the specified database file. Allocation units are divided into three partitions:
- IN_ROW_DATA
- LOB_DATA
- ROW_OVERFLOW_DATA
Each partition within index holds at least one IN_ROW_DATA unit. Depending on the schema of the index; it may also hold LOB_DATA as well as ROW_OVERFLOW_DATA.
Reasons for Occurrence of SQL Error 8905
The major cause of existence of this error message is the inappropriate functionality of any of the existing hardware components. This can be diagnosed by running Hardware Diagnostic Check. It may result in detecting the element that is actually responsible for the cause.
Resolution Techniques & Measures:
Backup Restoration: The error message that appears on SQL Server screen might take time to be resolved. This might further cause loss of information or inaccessibility of database components. Therefore, if any of the information reports to be lost; it can be restored back by restoring data from the most recent BAK files.
This way, the data that is been added to the database files since the last backup task was scheduled and implemented will only be compromised. Whereas all other components of data can be recovered using this method.
Hardware Diagnostic Check: As inappropriate functionality of the hardware component(s) is the main reason for the evolution of this error, running Hardware Diagnostics could help rectifying the issue.
This will help detecting the actual element that is responsible for causing interruption. Also, it will help taking further actions for rectification of error as per requirement and the level of damage. The particular part of the system hardware can be repaired or replaced for rectifying the issue.
Reasons for Occurrence of SQL Error 8905
Microsoft has confirmed that DBCC execution will not help in rectifying SQL Error 8905. Therefore, the above mentioned resolution methods can be implemented to get access to data that might have become inaccessible as an aftereffect of this error message.