DB Maintenance: Rebuild Index Task always fails on OperationsManagerDW [SCOM database]


Problem Description

=======================================


We encountered a situation where we were trying to execute a maintenance plan to rebuild the index of OperationsManagerDW database. However, the maintenance plan fails to complete.


 


In the Maintenance Plan log, the following paragraph was found:


Rebuild Index Task (<instance_name>)


Rebuild index on Local server connection


Databases that have a compatibility level of XX (SQL Server version XX.0) will be skipped.


Databases: All databases


Object: Tables and views


Original amount of free space


Task start: 2010-03-01T03:05:29.


Task end: 2010-03-01T03:08:48.


Failed:(-1073548784) Executing the query “ALTER INDEX [PK__EventSta__XXXXXXXXXXXXXXX] ON [E…” failed with the following error: “Cannot find index ‘PK__EventSta__ XXXXXXXXXXXXXXX ‘.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.


 


Additionally, the same error is encountered when you try to run a Rebuild Index script via SSMS


 


What’s happening here? Let’s see


 


What is causing this:


=======================================


§  To begin with, this was little strange as we verified that all indexes existed before started the maintenance plan


§  On further, probing we verified that, SCOM drops and recreates table “Event Stage” every minute


§  To verify, we generated T-SQL Script and copied the index alter script for problematic table


§  Also ran the script via SSMS and got same error


§  SELECT * from sys.objects where name like ‘Event Stage%’ gave us object id and creation date


§  Ran above SELECT again after a minute and now got a new name and creation date…..?


 


So here’s what is happening.


o    When we run a rebuild index script, it first collect index names and then tries to rebuild them.


o    In this specific case, it is getting an indexes name say PK__EventSta__XXXXXXXXXXXXXXX, however the table/index is getting dropped and recreated now with a different name.


o    This is causing the above error.


 


§  To confirm if the table has been modified, we also verified same using SQL Standard Report “Schema Change History”


 


Then how can I Re-index OperationsManagerDW database to ensure good performance


===================================================================


§  With a little more re-search; we verified that it is NOT required to Re-index OperationsManagerDW database as Reindexing is already taking place against the OperationsManagerDW database for some of the key tables. The functionality is built into the product. For more details, on OperationsManagerDW Self-maintenance refer this post


§  What we need to ensure – is that any default DBA maintenance tasks are neither redundant nor conflicting with SCOM OperationsManagerDW built-in maintenance.


 


Steps to reproduce the issue:


=======================================


1. On OperationsManagerDW SQL database


2. Create a simple maintenance with re-indexing task


3. “Select Report Options” choose a log location


4. Save and execute the maintenance plan and allow this to fail.


5. Verify the error in logs


 


Hope this will help!


 


Balmukund Lakhani & Varun Dhawan


Microsoft PSS



Reviewed by
Saket Suman & Amit Banerjee
Technical Lead, Microsoft SQL Server.


Comments (3)

  1. Boris says:

    Yeap, we've had the same issue and I've done some independant research that led me to the same findings. Great to be able to verify them.

  2. gurpreetsingh.sohal@hotmail.com says:

    Thanks for Sharing .. Very helpful

  3. Santosh says:

    We were running into the same issue, Thanks for sharing!