[Deprecated] Could not retrieve row from page by RID because the requested RID has a higher number than the last RID on the page

Summary

This error occurs when an attempt to retrieve a row from a data page by specifying the row ID (RID) failed, because the requested RID was a higher number than the last RID on the page. This can happen during normal processing, if the leaf page of a corrupt nonclustered index points to an incorrect or nonexistent RID on a data page.

Causes

If the error occurs on a system table during a read-only operation while other users are updating system tables (executing DDL), it is probably a transient 624 error (rather than a corrupted index). To confirm that there is no corruption, execute DBCC CHECKTABLE without a repair clause

Resolutions

The recovery procedure depends on when the error occurred. If the problems persist, the following procedures might not be sufficient to clean up the index corruption. In this case, contact your primary support provider. Have the output from either DBCC CHECKTABLE (if investigating possible system table corruption) or DBCC CHECKDB available for review.

If the error occurred during normal processing

Execute DBCC CHECKTABLE with the REPAIR_REBUILD clause. If executing DBCC CHECKTABLE with the REPAIR_REBUILD clause does not correct the problem, drop and re-create the affected index(es).

Important: If executing DBCC CHECKDB with the REPAIR_REBUILD clause does not correct the index problem, or if you are unsure what effect DBCC CHECKDB with the REPAIR_REBUILD clause has on your data, contact your primary support provider.

Index unknown, query known

The fastest way to resolve this problem is to execute DBCC CHECKDB with the REPAIR_REBUILD clause. This fixes any index corruption in the entire database. If the database is so large that you do not want to run DBCC CHECKDB, use these instructions to locate the specific index to drop and re-create.

If you do not know which index is causing the problem but you do know which query encounters the problem, follow the instructions below. If you do not know the index or the query, follow the instructions under the next section, "Index and query both unknown."

Determine which index should be dropped by reading the showplan output for the query that encounters the error. If you SET SHOWPLAN_TEXT to ON, SET NOEXEC to ON, and then run the query in question, the output indicates which nonclustered index the query is using to access the table in question.

For example:

USE pubs GO SET SHOWPLAN_TEXT ON GO SET NOEXEC ON GO SELECT title FROM titles WHERE title 'Cooking' GO

Here is the result set:

StmtText----------------SET NOEXEC ON(1 row(s) affected)

StmtText----------------SELECT title FROM titles WHERE title 'Cooking' (1 row(s) affected)

StmtText-------------|--Index Seek(OBJECT:([pubs].[dbo].[titles].[titleind]), SEEK:([titles].[title] ;[@1]) ORDERED FORWARD) (1 row(s) affected)

SET NOEXEC to OFF and SET SHOWPLAN_TEXT TO OFF again: SET NOEXEC OFF GO SET SHOWPLAN_TEXT OFF GO

Drop and re-create the index identified in Step 1 (in this example, titleind ).

Index and query both unknown

If you do not know the index or the query, rebuild all nonclustered indexes on the table as follows:

Look at the output you created with DBCC CHECKDB when you obtained the table name:

If the object ID is less than 100, you cannot drop and re-create the indexes. Do not continue with Steps 2 or 3. You must restore the database from a known clean backup.

If the object ID is 100 or greater, continue with Step 2.

Use sp_helpindex to list all indexes on the table, and then rebuild all the nonclustered indexes using one of the following methods:

Drop and re-create each nonclustered index on the table.

If the table has a clustered index, drop and re-create it. This causes all nonclustered indexes to be rebuilt automatically. If your table is large, you might not have the space to do this. Generally, 1.2 times of the size of the table must be available.

After the appropriate index has been re-created, run DBCC CHECKTABLE on the table to confirm that the problem has been resolved.

Overrideable Parameters