Rebuild Index Task failing for SCOM databases

Today I want to talk about the maintenance of the SCOM databases. I’ll like to focus on the Rebuild Index Task. After moving the SCOM databases to their own SQL Server instance, I start to notice that the Rebuild Index Task started to fail. When I looked closer to the problem I found out that one of the indexes was not being found and that caused the task to fail. Checking for this problem in Google helped me to find the cause of the problem and even better helped me to figure out the solution to the problem.

Google helped me to find a very helpful blog with the title “DB Maintenance: Rebuild Index Task always fails on OperationsManagerDW [SCOM database]”. That blog by Amrutha Varshini explains what does cause this problem and also give you a link to Kevin Holman’s blog “What SQL maintenance should I perform on my OpsMgr databases?”, this last blog goes even further in explaining what cause the problem. As you will conclude after reading Kevin Holman’s blog, SCOM does its own database maintenance for the OperationsManager and the OperationsManagerDW database. The last database, the OperationsManagerDW its being completely self-maintained by SCOM and that is why on that database Kevin advices to just do a “dbcc checkdb”. On the OperationsManager database Kevin advices to do normal maintenance, but to avoid doing the maintenance tasks at the same time SCOM does its maintenance. According to Kevin’s blog the SCOM maintenance on the OperationsManager database is executed every day at 02:30 in the morning, when I saw that I figure out why my problem started after moving the databases to their own SQL Server instance. My maintenance job started at 03:00, before moving the databases the task had to rebuild the indexes of another 3 databases and I think that was helping me to avoid the conflict between the self-maintenance task and my maintenance task. I change the startup time for the maintenance task and my problem was solved (I only do maintenance for the OperationsManager database, on the OperationsManagerDW database I just do a dbcc checkdb, as recommended by Kevin), after applying the changes my problem was solved. I hope this post will help you to solve your problem. If you have any question regarding this issue, do not hesitate to contact me, I’ll be glad to help.