Monday, August 24, 2009

How to Troubleshoot SQL Server Error 8910?

An SQL Server database is divided into pages each of 8 KB in size. An IAM (Index Allocation Map) page is the one that holds information about table extents or index information for each allocation unit. The page contains the location information of first 8 pages and also bitmap of extents for used extents. These are mixed pages but do not reference themselves. Among other things, an IAM page contains a mixed page array, which is actually an array of eight page IDs that holds mixed pages that have been allocated to the index. An SQL Server database might sometimes report corruption errors in these IAM pages. To restore lost information in such way, you will need to either use your recent database backup or use SQL Recovery tools to scan and repair the corrupted database.

For an example, you might come across the below error with your SQL Server database

“Server: Msg 8910, Level 16, State ST_NO Line L_NO
Page P_ID in database ID DB_ID is allocated to both object ID O_ID1, index ID I_ID1, and object ID O_ID2, index ID I_ID2.”

Where, ST_NO is the state of error message and can be either '1' or '2'.

Cause

If you receive the above error with state '1', this implies there exist two indexes that have their page allocated in either of mixed page array of IAM page. However, if you are getting this error with state '2' the reason could be that there exists a page that has been entered twice in mixed page array of an IAM page. These typically represent database in inconsistent state.

Solution

Follow these measures to solve the given problem:

Restore from backup if available and exists in valid state for complete restoration
Execute DBCC CHECKDB. Doing so will let your know the exact repair clause that should be applied to repair the database. Again, run DBCC CHECKDB with this clause.
Applying the above step might cause some data to be deleted. To prevent this, use safe SQL Repair tools that are competent to repair corrupted SQL Server database in any set of conditions. SQL Recovery software provide easy repair options through interactive interface and comprehensive recovery results.

SQL Recovery is an advanced SQL Server Recovery tool for damaged SQL Server databases. It supports repair for SQL Server 2008, 2005 and 2000. The software is available with self-descriptive interface and restores all database objects including tables, user defined data types and functions, triggers, stored procedures, views, rules etc. The software is compatible with Windows Vista, XP, 2003, 2000, and NT.

No comments:

Post a Comment