If you are upgrading SCOM 2012 to SP1 I strongly suggest reviewing this post from the SCOM Engineering Blog
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%\local\SCOM\logs)
Error: :Inner Exception.Type: System.Data.SqlClient.SqlException, Exception Error Code: 0×80131604, 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.
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