You are browsing the archive for Database.

Avatar of alkin

by alkin

Fix duplicate relationships for agents to server in Ops DB

9:34 am in Uncategorized by alkin

Sometimes it can happen that agents are ending up with multiple primary management server relationships. Of course you can only have one primary server relationship! In the rare occasions that you end up with multiple primary relationships you can now repair the issue by running the new “Fix duplicate relationships for agents to server in Ops DB” task manually


or there is a recovery on this monitor (disabled by default) that you can turn on so that the issue will be fixed automatically:




You will need the latest OpsMgr 2007 R2 management pack that you can find here

Alexandre Verkinderen

Avatar of alkin

by alkin

Windows Key Management Service MP for Operations Manager 2007

5:53 pm in Uncategorized by alkin

Microsoft just released a new KMS mp for opmsgr 2007. Be carefull when importing it via the catalog with the OpsMgr 2007 R2 console. Before importing the mp imageyou will need to uninstall any previous version of the mp AND you will also need to run a sql Clean up query to delete the previous KMS tables, KMS grooming jobs and any other KMS mp related stored procedure! This SQL cleanup query is not available when importing the management pack with the console. You will need to download and install the KMS mp from here:


Changes in This Update

The 6.0.7234.0 version of the KMS Management Pack includes the following changes:

· Support for Windows 7 and Server 2008 R2

· Decommissioning of KMS reports

· Support for non-Windows KMS applications

· Installation / un-installation changes

Supported Configurations

This management pack supports up to twenty KMS hosts. The following table details the supported configurations for the KMS Management Pack



Windows Server 2008 R2

Yes, all editions, 32-bit and 64-bit

Windows Server 2008

Yes, all editions, 32-bit and 64-bit

Agentless monitoring

Not supported

Windows 7

All volume editions

Windows Vista

All volume editions


Before you import the Key Management Service Management Pack, take the following actions:

· Remove any previous versions of the management pack at or below build # 6.0.6278.9

· If the previous version of the management pack utilized reports, follow these steps:

  1. Open SQL Query Analyzer and select the OperationsManagerDW
  2. From SQL Query analyzer, open the Uninstall.sql.txt (rename to uninstall.sql if desired) file attached with this management pack
  3. Execute (F5)
Avatar of alkin

by alkin

Opsmgr DB Create wizard System.InvalidOperationException

7:19 pm in Uncategorized by alkin

Today I wanted to install my OperationsmanagerDB on my SQL cluster 2005 SP2 actif-passive.

So I started the DBCreatewizard.exe and I got a very strange error mentioning something about


Severity: Error Message: Database creation failed. The database might have been incompletly created or modified. System.InvalidOperationException: An error occurred while trying to create the database on your SQL Server. Check your logs for more information. at Microsoft.EnterpriseManagement.Setup.DBCreateWizard.Program.LaunchDBCreation() at Microsoft.EnterpriseManagement.Setup.DBCreateWizard.SummaryPage.BackgroundThread()




To make a long story short:

We had a lab environment and production environment. Both environments are exact replicas: ip, users, servers, everything is the same. I did the install in the lab environment on my sql cluster and didn’t encounter any problems.

Now in production I had this strange error and asked my dba (that installed the sql cluster in lab and production) to confirm that everything was ok and that everything was the exact replica of the lab….

Now here comes the catch….and also the moment I wanted to kill my DBA….

I analyzed the dbcreatewizard error log that you can find here:


C:\Documents and Settings\user\Local Settings\Temp\1


And found the following sentence


10:31: CreateDB:  Attempting to create OperationsManager on ELSQL3E\WSSCOM threw the following sql exception Cannot use file ‘J:\SCOMOPLOG\OperationsManager.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.



I checked on my cluster resource and indeed: the Log disk was missing as a dependency!!


So my production environment was not the exact replica as my lab environment. I had to add the Log file disk as a dependency for my sql resource group:



And now I was able to run the dbcreatewizard.exe succesfully!




Hope this helps,

Alexandre Verkinderen

Avatar of alkin

by alkin

Optimizing the performance of your Opsmgr Console and reducing DB size

9:12 am in Uncategorized by alkin

I had troubles with the Opmsgr Console performance at serveral clients.

One of the most important things to boost your console performance is to reduce the Opsmgr DB size.


If you want to know which tabel is taking most of your DB size run the following query:

Simple query to display large tables, to determine what is taking up space in the database:

8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON ( =
WHERE ‘U’ = so.type GROUP BY  ORDER BY data_kb DESC



In my case the localizedtable was taking most of the disk space. Kevin Holman already blogged about this: 


I followed his procedure, I will describe all the steps I did and has to say I was really suprise by the results! :-)


  • Count the rows in the localizedtable

select count(*) from localizedtext


In a normal environment with the typical management packs imported the total count of rows should be around the million. In one of my environments it was 22 milion!! So I defenitely needed to clean up this localizedtable!


  • Look at the most common events in the localized table

select messageid, ltvalue, count(*) as Count from publishermessages with(nolock)
inner join localizedtext with(nolock)
on messagestringId = localizedtext.ltstringid
group by messageid, ltvalue
order by Count DESC


In my case it was the exchange management pack that produced the most ammount of events.


  • Run the clean up script

Kevin Holman has provided a script to clean up the localized table. You can download it from his blog .

Notice that this script is NOT supported by Microsoft, as it has not been thoroughly tested!  I’ve run the script in several environments now and didn’t run into issues but as a good Sys admin be carefull and take a backup of your opsmgr DB before running the script. Also make sure that your TEMPDB has enough space. And with enough space I really mean ENOUGH SPACE! Also the transaction logs of the TEMPDB will need a huge amount of free space. Also don’t run this scripts during the day when you have several operators running the opsmgr console. My CPU went crazy to 100 % processor time so my advice is to run the query at night or during the weekend.



  • Reindex the DB

USE OperationsManager
EXEC SP_MSForEachTable “Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (‘?’)”



Now the results! :-)


Before running the script:


After running the script:



Gain in diskspace:

OperationsManager free space in %:




OperationsManager free space in MB:






I was really suprised by the ammount of free space I had now. With cleaning up the localizetable I won about 60 % of space!


My Opsmgr console is running really smootly now!


Hope this helps,

Alexandre Verkinderen

Visit Us On TwitterVisit Us On Linkedin