Introduction and Tricks to Handle SQL Server Error 8910 ?
SQL Server is a Relational Database Management System that is commonly used by many organizations and Business Enterprises.The quality of excellent performance and low maintenance makes it advantageous which can either be utilized as as client-server model or as web based application.SQL server database consists of two types of files: Database File and Log File. The primary data file MDF and secondary data file NDF andboth hold tables, indexes, etc. The log file holds the transaction records.
SQL server deals with storage and management of huge amount of data.The chances of getting database corrupted is very high and it prompts different types of error messages. The SQL Server Error 8910 is an error that occurs due to flaw in IAM pages. IAM (Index Allocation Map)pages are internal structure which is used to hold page indexes. An IAM is of 4GB space and each 4GB memory spaces are called GAM chunks. IAM consists of two types of records i.e. a header and a bitmap. The details of error 8910 and possible solution are discussed here.
Error Message:
Error 8910 message shows the description as:
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.
Severity level: 16
Severity level indicates the type of problem with the error. Severity level 16 indicates errors are generated by user and user can handle it.
Behind the Error:
The SQL Server Error 8910 occurs if there is any flaw in entering the indexes of the page on IAM page Array. There can be two pages entered in same page or same page in two different IAM page array.
Solutions to SQL Server Error 8910
- Hardware level Failure:Troubleshoot the hardware in case of failure.
- Restore The Backup: If backups are available, restore it.
- CHECKDB:Execute DBCC CHECKDB to repair the corrupted files.
Deep into Solutions
Hardware level Failure:
Run Hardware Diagnostics to fix the hardware related problem. Go through the error logs of the SQL Server and Windows NT so that it gives a clear idea about the problem. Sometimes reinstallation of the Operating System may fix the problem. It will be better to replace the hardware if the problems exist frequently. The hardware problems can be because of
- Compressed drives.
- Improper settings and configuration of the hardware.
- Inefficient design of the hardware.
- Hardware that does not meet proper I/O or memory operations.
Restore The Backup:
If the backup for the database exists, restore the backup to obtain the database to healthy condition. To recover data under such instances the DBA must maintain a regular backup of the databases. The backup must be scheduled at regular intervals so that restoring database to latest backup is possible. It is always good to maintain a planned backup and restoring strategy. Backup is always essential to avoid data loss due to any data hazards.
DBCC CHECKDB:
DBCC CHECKDB is an inbuilt Query to check the integrity and object allocations of the database. This reports the issues and errors with the database. The syntax for DBCC CHECKDB is as follows:
DBCC CHECKDB
( 'database_name'
[ , NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
[ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
Database Name is the name of the database which is to be checked.Query checks
- Order of the indexes
- Index and page table mapping
- Consistency of pointers
- Validity of data in each page
- And page offsets.
This query works only if the degree of corruption is very less. Choose the option DBCC CHECKDB as a solution to error 8910 only if the Backup is unavailable.It is better to restore the backup if any error or corruption occurs in the database file.
Alternate solution
The DBCC CHECKDB works only to an extent.It cannot repair the highly corrupted MDF and NDF files. The third party commercial wares like SQL MDF Repair Tool, which is capable to recover the MDF and NDF data files, corrupted to any extent. This completely eliminates the SQL Server error 8910 from SQL server.