Wednesday, December 8, 2010

Fixing “Extra or Invalid Key...” Error Message

The information related to records in MS SQL Server database table is either saved in clustered or non-clustered index. In clustered index, the data rows in the table are stored with their unique key value. However, in non-clustered index, every data row in the SQL Server table consists of exactly one matching index row. The problem mainly arises when the non-clustered index consists of an index row, which does not match any data row in the table. This situation occurs when the database is corrupted or damaged. In such situations, the user receives a table error message that does not allows him/her to access the data saved in the table. To access the table data post SQL database corruption, the user needs to restore the data from an updated backup. However, if no backup is available or backup falls short of restoring the required data, the user needs to repair the database using SQL Repair application. Consider the below error message that appears when you try to access the data saved in your SQL Server table: “Table error: Database 'DBNAME', index 'OBJNAME.INDNAME' (ID O_ID) (index ID I_ID). Extra or invalid key for the keys” After the above error message, the records stored in the database table become inaccessible. Furthermore, no operations (add or update) can be performed on that table. Cause The above table error message appears when the index row in the non-clustered index does not match any data row in the table that you are accessing. This scenario occurs when the database is logically or physically damaged. Resolution The resolution for the above error message is repair of corrupted MS SQL Server database. To systematically do so, you need to follow the below measures: 1.Change the damaged physical system component with a brand new component. 2.Run DBCC CHECKDB command with repair clause in case of logical corruption of database.

No comments:

Post a Comment