May 19, 2008 at 9:57 am
Hi there,
We have a maintenance plan that as part of its steps does a rebuild of indexes. It seems to be working okay however we are seeing the following error in the log:
Executing the query "ALTER INDEX [PK__EventStage__004D6AA6] ON [Event].[EventStage] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "Cannot find index 'PK__EventStage__004D6AA6'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is quite strange because the index to which it refers doesn't exist!
Does anyone know of any reason why it might be trying to rebuild this index?
Many thanks,
Ian
June 27, 2008 at 8:29 am
Old thread, but I was having the same issue. Thought I would share what I found.
We recently deployed SCOM (Operations Manager, previously MOM) to a SQL 2005 cluster. I have my maintenance plan set to rebuild the indexes on all the databases every week. Every week since the deployment, it has failed. I checked the table and indeed, it was a different index than was trying to be rebuilt. I ran sp_helpindex again, and was shocked to see that the name had changed between the two executes.
Turns out that Microsoft has decided to rename the EventStage table to Event2Process then re-create the EventStage table during its processing. Unfortunately, in their DDL, they take the easy way out when creating the primary key and let SQL Server give it a name instead of naming it explicitly.
Given that this happens every minute (at least for me), the index that existed when the ALTER INDEX statement was created does not exist by the time it executes, creating the error.
To workaround the issue, I have temporarily excluded the DW database from the standard optimizations plan so that the other databases in that instance will continue to have their indexes rebuilt. I'll eventually create a separate rebuild for this database that excludes this table (and maybe others, I'll find out when I run the task).
If you're curious to see the DDL, launch profiler, add the SP:StmtCompleted event and filter TextData on %event%stage%
Kyle
June 27, 2008 at 12:16 pm
I have a stored procedure that I think could be useful for you. It is doing index optimization.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
One feature is that it is doing an extra check that the index still exist, just before it is doing the index rebuild or index reorganize. If the index doesn't exist, it continues to the next index.
Ola Hallengren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply