You are browsing the archive for Opsmgr DB.

Avatar of alkin

by alkin

Installing a remote Operations Manager database with DBcreateWizard.exe

11:56 am in Uncategorized by alkin

Hi all,

One of my customers was asking me if he could install the OpsMgr database remotely. This is perfectly possible with DBCreatewizard tool. The DBcreatewizard tool is a small tool that let you install the Opsmgr database and the datawarehouse without running the setupOM.exe wizard. Kevin Holman has blogpost on whether you should use the DBcreatewizard or just the good old setupOM.exe


By running the following command you can install the OperationsManager DB locally or remotely by specifying the severname:

DBCreateWizard.exe DBType:"Operations Manager Database" SQLInstance:OpsMgrSQserverL\infrontscomInstance DBName:OperationsManager ManagementGroup:OperationsManager UserGroup:Infrontscom\OpsMgrAdmins AutoErrorReport DBCreate DBSize:5000 DBPath:"C:\Program Files\Microsoft SQL Server\MSSQL10.INFRONTSCOM\DATA" LOGPath:"C:\Program Files\Microsoft SQL Server\MSSQL10.INFRONTSCOM\MSSQL\Log"




And for the Datawarehouse run the following command:

DBCreateWizard.exe DBType:"Operations Manager Data Warehouse Database" SQLInstance:OpsMgrSQLserver\infrontscomInstance DBName:OperationsManagerDW AutoErrorReport DBCreate DBSize:500 DBPath:"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" LOGPath:"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log"


In your local user temp folder C:\Documents and Settings\user\Local Settings\Temp\1  you will find a log file each time you run the dbcreatewizard tool.

Starting Logging for Setup.exe 13:19:14 dinsdag 4 januari 2011
13:19: GetlocalSQLServerPath: caught an exception: Invalid namespace
13:19: GetlocalSQLServerPath: caught an exception: Invalid namespace
13:19: GetlocalSQLServerPath: caught an exception: Invalid namespace
13:19: GetlocalSQLServerPath: caught an exception: Invalid namespace
13:19: Creating database…
13:21: Database created successfully.
Finished Logging for Setup.exe 13:21:19 dinsdag 4 januari 2011

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

Cleaning the Localizedtext Table after SCOM upgrade to SCOM 2007 R2

1:48 pm in Uncategorized by alkin

After upgrading your OpsMgr 2007 components and upgrading your SQL environment to SQL Server 2008, you will need to run some post installation steps. These post installation steps are only needed if you have done an upgrade from OpsMgr 2007 to OpsMgr 2007 R2.

OpsMgr 2007 SP 1 had an issue with the localized text table continuing to grow. The main cause of this was converted MOM 2005 management packs running a lot of backwards-compatibility scripts, such as the converted Exchange 2007 management pack. The issue was that each event wrote additional data to the localized text table, which is not groomed. Over time, the Operational database continued to grow, which had a tremendous impact on OpsMgr performance. Kevin Holman writes about this at; it is also documented on the SCUG.BE blog at .


I’ve just did an upgrade at a customer site and after completing the post-installation steps as mentioned here and I was very surprised by the results!!



I checked the database size and the localizedtext table before running the cleanup script.

  • Database size before the cleanup script


28 gig of space!


  • 12.700.000 records in the localizedtext table before running the cleanup script.


In OpsMgr 2007 R2, the localizedtext table issue is gone. The table still exists but Microsoft has resolved the growing issue. Even if the issue is gone you will need to run the following SQL query once after the upgade to initially clean up the localizedtext and publishermessage tables. You can copy/paste the script from here 


The script has run for about 4 hours!


Now the results! :-)


Disk space after the clean up script:


7 gig of disk space; So that’s a gain of 20 gig of disk space! That’s amazing!


780.000 records found in the localizedtext table after running the clean up script !! So that’s about 12 million records lesser!!


For completing the post-upgrade steps I did a reindex of the database.









And now my console is running very fast and I’ve less data to backup! So after upgrading to SCOM 2007 R2 you will definitely have to run the clean up script!!


Alexandre Verkinderen

Avatar of alkin

by alkin

Community Day Session: Setting up & Configuring SQL Server to support your SCCM & SCOM environment

2:41 pm in Uncategorized by alkin

Kim Oppalfens and I did a session at community day together:

Setting up & Configuring SQL Server to support your SCCM & SCOM environment

System Center Configuration Manager and Operations Manager both need SQL Server. In this session we will show you what the best practices are for having a smooth running SQL server for our System Center environment. Having a good initial setup and configuration is not the only important thing, doing regular health checks on your SQL server is even more important. In this session we will discuss just about anything you need to know about setting up SQL from the SCOM or SCCM admin perspective. In this session you will learn how to work with the SQL team and set up your scom or sccm to talk with their standard SQL setup. This includes setups with remote sql servers, sql server services running as domain users, and named instances. Additionally we will talk about some SQL related configuration topics, like network libraries, firewall configuration, SQL collations, database recovery models and authentication modes.

At the end of this session you will know everything you need to know about SQL setup to support your scom or sccm environment.

Speaker: Kim Oppalfens & Alexandre Verkinderen


Slide Deck: 


CommunityDay 2009

CommunityDay 2009

CommunityDay 2009

See you next year!

Alexandre Verkinderen

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