Lessons learned from SCOM SP1 Upgrade – Part 1

If you are upgrading SCOM 2012 to SP1 I strongly suggest reviewing this post from the SCOM Engineering Blog http://blogs.technet.com/b/momteam/archive/2013/01/16/patience-is-a-virtue-with-the-system-center-2012-operations-manager-sp1-installation.aspx

most importantly check that your SQL settings are correct before starting. another important note if the Management Server upgrade fails it will uninstall the management server so make sure you plan for this before you start just in case(more important if you are using AD integrated agent assignment).

I ran into a simular issue that is noted towards the end of the post when trying to upgrade my first Management Server. Once the upgrade wizard got to the database upgrade step it failed about 20 mins into the database upgrade. When reviewing the setup log I found the following error (the log file can be found in %appdata%localSCOMlogs)

Error:     :Inner Exception.Type: System.Data.SqlClient.SqlException, Exception Error Code: 0x80131604, Exception.Message:
The operation failed because an index or statistics with name ‘idx_StateDatabaseTimeModified’ already exists on table ‘dbo.State’.

This is the same error as the blog post talks about, but I didn’t start two upgrades at the same time as the blog post talks about this was the first time the database upgrade was attempted.

Support and I tried to follow the blog post to fix the issue but that didn’t work since the database upgrade had not completed all of the task.

When you run the build_mom_db.sql script it creates the index that is dropped in step 2 of the solution. This index can not exist durring the upgrade of the first management server to SP1.

Solution

1. Run the following SQL Statement on the SCOM Database

SELECT * FROM sysindexes where [name] = ‘idx_StateDatabaseTimeModified’ AND id = object_id(N'[dbo].[State]’)   if it returns the 1 record indicating that the index does exist in the database.

2. Run the following SQL Statement on the SCOM Database

IF EXISTS (SELECT * FROM sysindexes where [name] = ‘idx_StateDatabaseTimeModified’ AND id = object_id(N'[dbo].[State]’))
DROP INDEX [idx_StateDatabaseTimeModified] ON [dbo].[State]

Then start the upgrade to SP1 again, it should complete now.

Come back in a few days for part 2 where I will explain how to fix your management server if the upgrade is finding the SCOM 2012 Agent installed

3 comments

  1. Thank you so much! I thought I was going mad when the management server removed itself. I have restored the Management server but now the DB is ‘out of sync’ with the MS. I’ll follow your instructions and let you know how I got on. Deffo bookmarked this page.

    1. if you have other questions about errors you run into, feel free to post and I will let you know if I can help, I had multiple issues upgrading to SP1 and will be blogging about them soon

Leave a Reply

Your email address will not be published. Required fields are marked *