Index Corruption.

Applies to: Microsoft SQL Server 2000 and above.
 

Error Message.


Examining the SQL Server Error Log we found the following  records:

Date 11/6/2008 3:07:07 PM
Log SQL Server (Current - 12/3/2008 3:00:00 AM)

Source spid53

Message
Could not find the index entry for RID '160314a879010000' in index page (1:428288), index ID 3, database 'DATABASENAME'..


Cause.

In this case, this error appeared due to a index corruption. The index is nonclustered, because the index ID is greater than 1. Rebuilding the index should solve the problem.
 

Solution.

Follow these steps:

1. Run the DBCC CHECKDB command to check the status of the database using SQL Server Management Studio. For example:

DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS
 

The results are supposed to tell you that there are errors found on the database. For example:

DBCC CHECKDB (DatabaseName) executed by Domain\UserName found 1 errors and repaired 0 errors. Elapsed time: 0 hours 4 minutes 3 seconds.

2. Rebuild all indexes on the database using the statement DBCC DBREINDEX if your are using SQL Server 2000, for example: DBCC DBREINDEX(@TableName,' ',90), where 90 is the fill factor. If you are using SQL Server 2005 or above, please try to use the ALTER INDEX statement, for example: ALTER INDEX ALL ON TableName REBUILD. This statement will rebuild all index on the table "TableName". An alternative to the previous two statements is the DBCC CHECKDB command using the REPAIR_REBUILD option, but it needs the database to be set in single user mode. This statement can be used in the following manner: DBCC CHECKDB ('DatabaseName', REPAIR_REBUILD).

3. Run the DBCC CHECKDB command to verify there are no errors left on the database.

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.