Grooming process in the Operations Database

This is a continuation of my other post, on general alert grooming:

How grooming and auto-resolution work in the OpsMgr 2007 Operational database


Grooming of the OpsDB is called once per day at 12:00am…. by the rule:  “Partitioning and Grooming” You can search for this rule in the Authoring space of the console, under Rules. It is targeted to the “Root Management Server” and is part of the System Center Internal Library.



It calls the “p_PartitioningAndGrooming” stored procedure.  This SP calls two other SP’s:  p_Partitioning and then p_Grooming

p_Partitioning inspects the table PartitionAndGroomingSettings, and then calls the SP p_PartitionObject for each object in the PartitionAndGroomingSettings table where "IsPartitioned = 1"   (note – we partition event and perf into 61 daily tables – just like MOM 2005)

The PartitionAndGroomingSettings table:



The p_PartitionObject SP first identifies the next partition in the sequence, truncates it to make sure it is empty, and then updates the PartitionTables table in the database, to update the IsCurrent field to the next numeric table for events and perf.  Then it calls the p_PartitionAlterInsertView sproc, to make new data start writing to the current event and perf table.

To review which tables you are writing to – execute the following query:   select * from partitiontables where IsCurrent = ‘1’

A select * from partitiontables will show you all 61 event and perf tables, and when they were used.  You should see a PartitionStartTime updated every day – around midnight (time is stored in UTC in the database).  If partitioning is failing to run, then we wont see this date changing every day.  


Ok – that’s the first step of the p_PartitioningAndGrooming sproc – Partitioning.  Now – if that is all successful, we will start grooming!

The p_Grooming is called after partitioning is successful.  One of the first things it does – is to update the InternalJobHistory table.  In this able – we keep a record of all partitioning and grooming jobs.  It is a good spot check to see what’s going on with grooming.  To have a peek at this table – execute a select * from InternalJobHistory order by InternalJobHistoryId



The p_Grooming sproc then calls p_GroomPartitionedObjects  This sproc will first examine the PartitionAndGroomingSettings and compare the days to keep column, against the current date, to figure out how many partitions to groom.  It will then inspect the partitions to ensure they have data, and then truncate the partition, by calling p_PartitionTruncate.  The p_GroomPartitionedObjects sproc will then update the PartitionAndGroomingSettings table with the current time, under the GroomingRunTime column. 

Next – the p_Grooming sproc continues, by calling p_GroomNonPartitionedObjects.  p_GroomNonPartitionedObjects is a short, but complex sproc – in that is calls all the individual sprocs listed in the PartitionAndGroomingSettings table where IsPartitioned = 0.  (see my other post at the link above to follow the logic of one of these non-partitioned sprocs)

Next – the p_Grooming sproc continues, by updating the InternalJobHistory table, to give it a status of success (StatusCode of 1 = success, 2= failed, 0 appears to be never completed?)


If you ever have a problem with grooming – or need to get your OpsDB database size under control – simply reduce the data retention days, in the console, under Administration, Settings, Database Grooming.  To start with – I recommend setting all these to just 2 days, fromt he default of 7.  This keeps your OpsDB under control until you have time to tune all the noise fromt he MP’s you import.  So just reduce this number, then open up query analyzer, and execute p_PartitioningAndGrooming  When it is done, check the job status by executing select * from InternalJobHistory order by InternalJobHistoryId   The last groom job should be present, and successful.  The OpsDB size should be smaller, with more free space.  And to validate, you can always run my large table query, found at:   Useful Operations Manager 2007 SQL queries

Comments (5)

  1. Anonymous says:

    I’ve run across a couple instances where it appeared that alerts were not being groomed from the Operations

  2. G.R.V says:

    Hello Kevin,

    When I run this query: select * from InternalJobHistory order by InternalJobHistoryId

    I find 0 under StatusCode instead of 1. I guess there is some problem in database grooming. There is no error in event logs. Help me fix this.

  3. xavi2 says:

    Hello Kevin,

       I want to check if My Operational datasets are groomed and I want to check the real days to keep and data. In other words, it is possible to know the oldest and newest recorded timestamps of each data type in the operational Database?

    thank you.

  4. Maekee says:

    Which database-permissions is needed to run this databasegrooming

Skip to main content