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

  • Article

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.