unable to find index entry...

Question

We have a third party database that is generating the following error:

"Unable to find index entry in index ID 1, of table 622625261, in database 'DatabaseName'.The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support."

This happens at the exact same time every night, so it may be related to a clean up process.  It's my understanding that this may be related to update/deletes using joins.  The index involved varies, but it is always a date field.  DBCC checktable doesn't fix it, rebuild doesn't fix it.  I have to drop and recreate the index before I can run a clean dbcc checktable.  It's a sql server 2008 (10.0.400) and the date type on the column is datetime.  Interestingly, the column name is a reserved word, datetime.  And.. the non clustered index is on the same column that is part of a compound primary key.

2 questions:

1)  Is a SQL statement causing this, or is it something with the index structure, the fact that the column name is a reserved word or that the column indexed is part of a primary key?

2)  Right now, I'm fixing this problem by manually dropping/recreating the index... I've looked around in sys.indexes and sys.sysindexes and I don't see a way to find a corrupted index and build an automated process to fix this until I find a more permanent solution.

Thanks for anyone's time.

Ria

---

Ria Caussyn

Answer 1

Hi Ria,

Is this on a view or on a table? Reason I ask is it might be due to this bug

http://support.microsoft.com/kb/2248999

Apparently the fix is to patch to the latest version and run checkdb. Might not apply to you though if it's on the tables themselves.

Thanks,

Stephen

---

Please click "Mark as Answer" if you found my post helpful. Thanks, Stephen.

Answer 2

It looks like SQL Server 2008 SP3 contains this fix.  I'll apply this and see how it goes.  I'll post the result.

---

Ria Caussyn

Answer 3

Applied SP3 last week and there haven't been problems.. so that appears to have fixed it.

---

Ria Caussyn