subject: Tips To Resolve chain Sequence Numbers Are Out Of Order... Error Message [print this page] An IAM (Index Allocation Map) page in MS SQL Server database is a metadata entry that maps the extents in a 4GB part of database file. This database file is used by three types of allocation units-IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. An IAM page includes a header to indicate the beginning range of Extents mapped by that particular IAM page. Furthermore, every IAM page contains a sequence number, which acts as its exact position within the IAM chain. Corruption or damage in the sequence numbers primarily results in inaccessibility of records stored in the database. In such situations, if you want to access your data, then you will need to restore the data from an updated backup. However, if no backup is available or backup itself is corrupted, then you will need to use powerful SQL Recovery software.
Consider a practical scenario, where you encounter the below error message when you attempt to access your database:
Chain sequence numbers are out of order in IAM chain for object ID O_ID, index ID I_ID. Page P_ID1 sequence number SEQUENCE1 points to page P_ID2 sequence number SEQUENCE2.
Your SQL database records become inaccessible after the above error message pops up. To add to your problems, the same error message reappears each time you attempt to access your SQL database records.
Cause:
The fundamental reason for the occurrence of the above error message is metadata corruption i.e the sequence numbers of the IAM pages is corrupted. Corruption can occur due to various hardware and software factors.
Resolution:
For complete troubleshooting of the above error message, you will need to firstly analyze the exact reason for the corruption of sequence numbers of IAM pages. If the corruption is caused due to hardware failure, then change the hardware component. However, in case of software corruption, run DBCC CHECKDB command with appropriate repair clause to repair the database.
DBCC CHECKDB command allows complete repair in most cases. But, if in case the command fails, then the only option is to use a advanced SQL Recovery software. Such SQL Repair applications employ high end scanning mechanisms to repair the database.
