Optimizing the performance of your Opsmgr Console and reducing DB size

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:

SELECT so.name,
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 (si.id = so.id)
WHERE ‘U’ = so.type GROUP BY so.name  ORDER BY data_kb DESC

 

In my case the localizedtable was taking most of the disk space. Kevin Holman already blogged about this: http://blogs.technet.com/kevinholman/archive/2008/10/13/does-your-opsdb-keep-growing-is-your-localizedtext-table-using-all-the-space.aspx 

 

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 DeletePublisherMessages.zip .

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
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
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