donderdag 17 januari 2013

Invalid column name MPResourceElementId Operations manager 2012 SP1

We have a 3 server Operations manager environment to monitor our customers.
Last week I started upgrading the servers to System Center SP1.

First I created back-ups of all the databases and VM’s.
( OperationsManager, OperationsManagerDW, Reporting )

After that I started upgrading the first management server.
The upgrade of the first management server went OK, this upgraded the server and Operations Manager databases. Great, everything was still working.

When upgrading the second server, the setup showed me an error upgrading the database.
Wait, wasn’t this already upgraded? I restored the VM and started over, this time successfull.
After testing I realized it did not work as expected.

When viewing task status and alerts the console started giving me all kind of errors.
” Invalid column name ProgresValue”  and “ Invalid Clumn name “MPResourceElementId” .


At the time of the install, there was nothing to be found on the internet about this error.
So I restored the VM’s and Databases as I did not have the time to troubleshoot.
Fortunately, now there is!

Yesterday J.C. Hornbeck posted the solution on technet blogs.

A quote :

“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.”

So I was a little impatient.

He also provides a solution if you were impatient like me Glimlach.

To get this resolved, do the following:

“ 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.”

The above solution is provided by J.C. Hornbeck, knowledge Engineer @ Microsoft.

Source :

1 opmerking: