Error 624

Severity Level 24

Message Text

Attempt to retrieve row from page via RID failed because the requested RID has a higher number than the last RID on the page. %S_RID.%S_PAGE.

Explanation

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.
• During database recovery, if the data structure that contains the last checkpoint record in the transaction log points to an incorrect or nonexistent record in the log. The database recovery process occurs during startup and when a load database statement executes.

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, run DBCC CHECKTABLE.

Action

The recovery procedure depends on when the error occurred. If 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 the appropriate DBCC statements available for review.

If the error occurred during normal processing

For instructions on using the dbcc page statement to determine the table involved, see "Associating a Page with an Object" in Chapter 24, "Additional Problem-solving Techniques." Save this information and use it in the appropriate section below.

The specific action you take depends on whether or not you know which index and/or query caused the problem. In general, dropping and re-creating the index should resolve the index corruption.

Index unknown, query known

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 don't know the index or the query, follow the instructions under the next section, "Index and query both unknown."

1. Determine which index should be dropped by reading the showplan output for the query that encounters the error. If you set SHOWPLAN on, set NO EXECUTE on, and then run the query in question, the output will indicate which nonclustered index the query is using to access the table in question.

   For example:

   set showplan on
   go
   set noexec on
   go
   select title from titles where title > "Cooking"
   go
   STEP 1
   The type of query is SELECT
   FROM TABLE
   titles
   Nested iteration
   Index: titleind

2. Set NOEXEC off and showplan off again:

   set noexec off
   go
   set showplan off
   go

3. 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:

1. Look at the output you created with dbcc page 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.
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, 120 percent of your table size must be available.
3. After the appropriate index has been re-created, run dbcc checktable on the table to confirm that the problem has been resolved.

If the error occurred during database recovery

When this error occurs during recovery, the database is marked "suspect" and is not accessible. In this case, you usually need to load the database from a known clean backup, as follows:

1. Drop the suspect database using the instructions in Chapter 24, "Additional Problem-solving Techniques."
2. Create a database to receive the load using the CREATE DATABASE statement.
3. Load the database from a backup using the load database statement.

For information about using the CREATE DATABASE and LOAD DATABASE statements, see the Microsoft SQL Server Transact-SQL Reference.