Now that we have publically released System Center 2012 Service Pack 1, you need to take care when applying this to your Operations Manager servers. As part of the Service Pack installation we make some changes to the OperationsManager database objects, adding additional indexes, etc... As all the management servers share the same database, these changes only need to be made when installing the Service Pack on the very first server you install to. When you run the Service Pack 1 installation it communicates with the database to determine if this is the first installation, and then decides if it needs to run a SQL update script or not.
This means that you need to be patient with your installation and wait for the first management server to have Service Pack 1 installed before beginning the next installation. If you don’t then the SQL script will run again and issues may occur. It is very early on in the Service Pack installation process that the management server checks to see if the DB has already been upgraded, so do not be tempted to tee up your other management servers and run through the initial wizard while you wait for the first server to install because by then it will be too late.
Additional things to double check before proceeding with the install
As you will be making changes within SQL, ensure the account you are using to run the first Management Server installation is a SysAdmin on the SQL server hosting the OperationsManager DB.
Additionally, for the changes to succeed we need to ensure ad hoc changes are allowed to the SQL server system catalogs as per http://support.microsoft.com/kb/2713047
So before you begin installing Service Pack 1 verify if you are ready to proceed by running the query below against the Master database:
exec sp_configure 'show advanced options', 0 RECONFIGURE
If the command fails with the message "Exception.Message: Ad hoc update to system catalogs is not supported" then follow the resolution steps in http://support.microsoft.com/kb/2713047 before launching the install, i.e. run
exec sp_configure 'allow updates',0 RECONFIGURE
Oops, that information comes a little too late for me…
So what happens if you already clicked install for the Service Pack on a second management server before the first has completed the DB changes?
Well the script will run twice and you may see the following symptoms:
1. When attempting to view Task Status in the OpsMgr console it presents the following error:
Invalid column name ‘ProgressValue’
2. When you right click on an Alert you see the following error:
Invalid column name “MPResourceElementId”
3. The Management Server Health Service Watcher State is gray for all Management Servers.
In the OpsMgrSetupWizard installation log on the second management server to attempt the DB changes you will find an error similar to this below:
[12:31:41]: 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'.
OK, so now what do I do…
If this is the situation you find yourself in, take the following steps to remedy the situation:
1. Stop all OpsMgr services that access the DB (the System Center Data Access Service, the System Center Management Service and the System Center Configuration Service on all Management Servers).
2. Run the following SQL commands:
USE OperationsManager IF EXISTS (SELECT * FROM sysindexes where [name] = 'idx_StateDatabaseTimeModified' AND id = object_id(N'[dbo].[State]')) DROP INDEX [idx_StateDatabaseTimeModified] ON [dbo].[State]
3. Next run the build_mom_db.sql script from the Service Pack Setup directory against the OperationsManager DB.
4. Finally, restart all of the OpsMgr services.
I hope this post helps you avoid this potential problem, and if it comes too late, I hope it provides you with the information you need for a swift resolution.
All the best,
Brian McDermott | Escalation Engineer | Management and Security Division
Get the latest System Center news on Facebook and Twitter:
System Center All Up: http://blogs.technet.com/b/systemcenter/ System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/ System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/ System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/ System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/ System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm
Windows Intune: http://blogs.technet.com/b/windowsintune/ WSUS Support Team blog: http://blogs.technet.com/sus/ The AD RMS blog: http://blogs.technet.com/b/rmssupp/
The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/ The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/ The Forefront TMG blog: http://blogs.technet.com/b/isablog/ The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/
ran in to this but my first server had installed ok, however the install for my #2 and #3 MS's overlapped (just) and then this issue occurred ... patience for sure! One at a time!
BTW, your resolution above worked a treat!
Brilliant article, the batch file resolved this annoying issue, thank you a lot!