Damaged Indexes in SQL Server

If you ever see a message like this in the Windows Event log or in the SQL Server log:

"Could not find the index entry for RID ‘xxxxxxx’ in index page 1:10098766, index ID 5, database ‘ngdatabasename’"

It means that you have a damaged index. The problem is figuring out which index is damaged. Using the procedure below will let you determine that.

 

— Turn on Trace flag 3604

DBCC TRACEON(3604)

 

— Run DBCC PAGE, with the database name, and the index page number (with no 1:)

DBCC PAGE (‘ngdatabasename’, 1, 13625149 , 0)

 

–In the results, look for MyObjID and on that number, use the OBJECT_NAME function to get the table name

SELECT OBJECT_NAME(511340886)

 

— Use the same MyObjID for the id value, and use the Index ID from the error message for the indid value to get the index name

SELECT Name

FROM dbo.sysindexes

WHERE indid = 0

AND id = 511340886

 

After you figure out what index is damaged, the easiest way to fix it is to drop and recreate the index, using T-SQL like you see below:

 

IF EXISTS (SELECT NAME FROM dbo.sysindexes WHERE (name = N’IX_CurrentPost_LinkUrlID’))

BEGIN

DROP INDEX dbo.CurrentPost.IX_CurrentPost_LinkUrlID

END

GO

CREATE NONCLUSTERED INDEX IX_CurrentPost_LinkUrlID ON dbo.CurrentPost

(LinkUrlID) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s