List Of Few Common MS SQL Errors

MS SQL Errors:

1. Unable to process table O_NAME because filegroup F_NAME is offline.
2. Unable to process index I_NAME of table O_NAME because filegroup F_NAME is invalid.
3. Table error: Page P_ID with object ID O_ID, index ID I_ID in its header is allocated by another object
4. Table error: Object ID O_ID1, index ID I_ID1 cross-object chain linkage. Page P_ID1 points to P_ID2 in object ID O_ID2, index ID I_ID2.
5. Could not find a table or object named '%.*ls'. Check sysobjects
6. Index 'INDEX_NAME' on table 'OBJECT_NAME' is marked offline. Rebuild the index to bring it online.
   
7. Object ID O_ID, index ID I_ID: Page P_ID is empty. This is not permitted at level LEVEL of the B-tree.
8. IAM page P_ID1 is pointed to by the next pointer of IAM page P_ID2 object ID O_ID index ID I_ID but was not detected in the scan.
9. Table error: Object ID O_ID, index ID I_ID, page P_ID. Test (TEST) failed. Values are VAL1 and VAL2.
10. Table error: Page P_ID with object ID O_ID, index ID I_ID in its header is allocated by another object.
11. Table error: PAGETYPE page P_ID (object ID O_ID, index ID I_ID) is out of the range of this database.
12. Table error: Object ID O_ID, index ID I_ID. B-tree chain linkage mismatch. P_ID1->next = P_ID2, but P_ID2->Prev = P_ID3.
13. Table error: Object ID O_ID, index ID I_ID, page ID P_ID1. The PageId in the page header = P_ID2.
14. Table error: Allocation page P_ID has invalid PAGE_TYPE page header values. Type is TYPE. Check type, object ID and page ID on the page.
15. Table error: Object ID O_ID, index ID I_ID. B-tree page P_ID1 has two parent nodes P_ID2, slot S_ID2 and P_ID3, slot S_ID3.
16. Extent E_ID in database ID DB_ID is allocated to more than one allocation object.
17. Extent E_ID is allocated to 'OBJECTNAME' and at least one other object.
18. Database 'Employee' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKNAME processing.
    
19. Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not allocated in any IAM. PFS flags 'PFS_FLAGS'.
20. IAM page P_ID for object ID O_ID, index ID I_ID controls pages in filegroup FG_ID1, that should be in filegroupFG_ID2
21. Row error: Object ID O_ID, index ID I_ID, page ID P_ID, row ID S_ID. Column 'COLUMN' was created NOT NULL, but is NULL in the row.
22. Database ID DB_ID, object 'OBJNAME' (ID O_ID). Loop in data chain detected at P_ID.
23. Object ID O_ID, forwarding row page P_ID1, slot S_ID1 points to page P_ID2, slot S_ID2. Did not encounter forwarded row. Possible allocation error.
24. Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID DB_ID pages from P_ID1 to P_ID2. See other errors for cause.
25. Incorrect PFS free space information for page P_ID, object ID O_ID, index ID I_ID, in database ID DB_ID. Expected value PFS_VAL1, actual value PFS_VAL2.
26. Database error: Page P_ID1 is marked with the wrong type in PFS page P_ID2. PFS status 0xVAL1 expected 0xVAL2
    

Tips to resolve “Chain sequence numbers are out of order...” Error Message

An IAM (Index Allocation Map) page in MS SQL Server database is a metadata entry that plans the extents in a 4GB part of database file. This database file is used by three types of allocation units they are 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. Moreover, every IAM page contains a sequence number, which acts as its exact position within the IAM chain. Corruption in the sequence numbers primarily results the inaccessibility of records stored in the database. In such situations, if you want to access your data, then you need to restore the data from an updated backup. However, if no backup is available or backup it self’s corrupted, then you need to use powerful SQL database recovery software.

Consider a practical scenario, where you meet the below error message when you try 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 database records become inaccessible after getting the above error message pops up. The same error message reappears every time when you try to access your database records.

The primary reason for the occurrence of the above error is metadata corruption i.e the sequence numbers of the IAM pages is corrupted.

For complete troubleshooting of the above error message, you need to analyze the exact reason for the corruption of metadata (sequence numbers of IAM pages). If the corruption is 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 the command fails, then the only option is to use advanced SQL Recovery software. Such SQL Database Recovery applications employ high end scanning mechanisms to repair the database.