Monday, August 10, 2009

Resolving Error 8904 in SQL Server

In SQL Server database, there are few types of pages that are used to store allocation structures. A GAM (Global Allocation Map) page is the one that has an individual bit for each extent included in the file interval that GAM page maps. Whereas, SGAM (Shared GAM) has similar structure as of GAM but has little dissimilarity like in SGAM, each of set bits corresponds to an extent, where each of the pages is independently allocated to IAM pages for different indexes. An IAM page also retains the similar structure as of GAM page, but has one dedicated bit for an extent that is allocated to the index (here, the discussed index is the one of which IAM page is a part). But sometimes, you get table corruption errors that show that two different IAM pages have been allocated the same extent. In such situations, you are recommended to immediately restore your data from backup or use third-party SQL recoveryapplications to scan and repair such databases.

You might receive the below error with your SQL Server database:

Server: Msg 8904, Level 16, State 2
Extent E_ID in database ID DB_ID is allocated to more than one allocation object.
You might also get error 8913 explaining the details of indexes involved.
Cause
The above error signifies that the bits are in state 2 in the specified table, but with two different IAM pages while the same extent is allocated to both of them. These conditions usually arise due to hardware failure reasons.
Solution
It is suggested to first check your system for hardware failures. To do so, you should run hardware diagnostics, check the application and Windows error logs for the related hardware failure errors.
To cope up with database issues, use your recent data backup
If no clean backup is available, you should run DBCC CHECKDB command without using any repair clause. This will determine the extent of corruption and repair clause that should be used. Again run DBCC CEHCKDB but with using suggested repair clause. But the process can delete the corrupted database items while making it error-free.
To perform safe and efficient database repair, you need to use commercial SQL Repair utilities. These are designed as read-only applications that you can use to get complete SQL Recovery results.
Stellar Phoenix SQL Recovery is an advanced tool that repairs and restores damaged SQL Server database created with SQL Server 2008, 2005 and 2000. It is a comprehensive database repair product for all database objects and is embedded with unique set of features. It is compatible with Windows Vista, XP, 2003, 2000 and NT.

No comments:

Post a Comment