All You Need to Know About SQL Error 8905

In SQL Server, GAM allocates approximately 4 GB per extent corresponding to 1 bit per extent in file interval. It holds one bit\extent (1bit per extent). So, if the extent is empty, a bit is assigned to it and if it is allocated, the bit is not set to it. The SQL Server error 8905 appears similar to this:

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.

SQL databases are prone to errors and such messages takes place very frequently. To understand this error message, it is important to understand the terms associated to it such as GAM, SGAM and IAM.

GAM: Global Allocation Map

GAM page keeps track of whatever allocated to extents. It holds around 64000 extents (equal to 4 GB data).

SGAM: Shared Global Allocation Map

SGAM page records the information about the extents that are being in used currently as "mixed" along with an unused page.

IAM: Index Allocation Map

IAM page tracks information or records corresponding to GAM intervals. It keeps track of each single entity of a file.

Cause & Resolution For SQL Error 8905

There are many reasons responsible for the occurrence of the above stated error. The causes and the resolution are mentioned herein:

Hardware Inadequacy: Major reason behind this error message is the hardware inadequacy or inappropriate functionality of any related component. Such deficiency can be examined by "Windows Application Log" and "SQL Server Error Log" and can be eradicated by running Hardware Diagnostics.

If any of the hardware components seems to be working in an inappropriate manner, replacing that particular part could result in elimination of corresponding error message. Moreover, try to format the drive or reinstall the Operating System to remove the message.

Backup Restoration: If the problem behind the existence of SQL Server error 8905 is other, then it is recommended to restore the entire database from available clean backup.

Note: It is a good practice to backup SQL database on periodic basis.

Database Console Commands: Initially, execute the DBCC CHECKDB without repair switch to detect the level of corruption. Running the DBCC CHECKDB commands without any clause will suggest the most preferred clause for rectifying the issue. Once, it recommends the repair clause, run DBCC with the suggested clause to repair the damaged SQL tables.

Following syntax can be used for executing DBCC:

Note: Provide appropriate database name while executing the above mentioned syntax.

Relevant Information

Sometimes, running DBCC may not resolve the issue; instead it may leads to occurrence of SQL Server error 8909. Rectifying SQL error 8905 involves resolving other errors first. The command may state that the error has been resolved but in actual the problem may not have been eradicated.

This situation will take place with MS SQL Server 2000 version but can be resolved by installing its 'Service Pack 2'. Afterwards, execute DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS clause for repairing the issue.

Alternate Solution

However, performing DBCC CHECKDB with repair clause (repair_allow_data_loss) may result in loss of data. Any kind of loss could leads to inaccessibility of entire database. So, it is important to rescue the records of database tables by executing a simplified and a straightaway solution.

One such solution is SQL Server database recovery tool that helps in removing "SUSPECT" status from databases and bring them back to normal mode. The tool helps in eliminating maximum error messages including SQL Server error 8905 from SQL Server 2000, 2005, 2008, 2008(R2), 2012, 2014 and restore the data back into their respective formats.