SCOM 2007 – operational and datawarehouse grooming

The mechanism for grooming the operational database in SCOM has changed compared to MOM 2005.  In MOM 2005 we had a SQL job to handle the grooming – in SCOM 2007 we have a SCOM rule.  The rule has a set schedule to run daily at midnight – and there is no built in override to allow this schedule to be changed.   In some situations, it’s helpful to manually force grooming.  This can be done by manually executing the p_partitioningandgrooming stored procedure.  This will call a series of other procedures which will grab your groom settings (retention threshold values) from the database and proceed with the groom. 

The mechanism for grooming the datawarehouse is a bit more involved.  First, there is no way to set the data retention period for the warehouse in the UI.  These retention periods are stored in the MaxDataAgeDays column in the StandardDatasetAggregation table of the warehouse.  You can set these however you like.  From there, grooming (and aggregation actually) happens every time data is inserted into the warehouse.  To be sure though, you can also trigger it to happen manually by running the following stored procedure from the data warehouse. 

Exec standarddatasetgroom <datasetid> 

The dataset ID’s represent the type of data to be acted on.  You can grab the datasetid from the dataset table which is also in the datawarehouse.  If you open this table you will note there are 5 dataset types – event, client monitoring, performance, alert and state. 

When SCOM actually does the aggregation and groom job it calls the standarddatasetmaintenance stored proc which will result in the standarddatasetgroom procedure getting called.  For grooming, we don’t care about aggregation so we can just call the groom job directly.

Comments (16)

  1. 1. How does reindexing work in opsmgr? Is it a SQL job? Which one? Is it a rule in the SQL management

  2. steverac says:

    This was a while back but just noticed it.  Reindexing is handled by an internal OpsMgr job that runs on the RMS at 2:30 AM daily.  This is not configuraable.  Unlike MOM 2005, there s no sql job listed in the SQL console – just an Opsmgr rule that fires the appropriate stored procedure.  The reindex job doesn’t reindex everything but it cleans up most data.

  3. I came across this post on Steve Rachui&#39;s Manageability blog – ConfigMgr/OpsMgr : “ A while back

  4. I came across this post on Steve Rachui&#39;s Manageability blog – ConfigMgr/OpsMgr : “ A while back

  5. Ravigupta1 says:

    Is there any way to check how much data is groomed (i.e. no or rows) from OpsDB/Data Warehouse DB and in which grooming job?

  6. steverac says:

    The tables you likely care about are in the operational database containing event and perf data.  Those tables aren’t ‘deleted’ per se – they are truncated as a whole, per day.  The only way to see the amount delete would be to take a row count on the specific table before the groom job runs.  The other steps of the groom processes will actually delete so if you were to run them manually (not generally recommended or needed) then you could see the rows deleted.

  7. Ravigupta1 says:

    Thanks for the prompt response!

    I have set Data retention for the Data Warehouse DB (in database) for 90 days. earlier it was default. it is been 5-6 days when i changed the retention days still i have data earlier that 90 days. Please suggest where to check in DB for any possible error.


  8. steverac says:

    Earlier than 90 days?  Seems to me that is what you want, right?  You set grooming to retain data for only 90 days but your data hasn’t reached 90 days old yet?  Am I missing something?

  9. Ravigupta1 says:

    yes, you are right.

    I have changed the DW retention period from default (400 days) to 90 days (for all type of data). But still the data is not yet groomed. we have data in DW Db which is more than 90 days old. Infact, i was checking the Alerts data and not a single alert is groomed out.

  10. steverac says:

    OK, so with these settings changes grooming should happen automatically and adjust to the 90 day threshold.  You can run grooming manually against the datawarehouse as a troubleshootng step to see if an errors are logged.  Note in my blog above where I indicate that you can run

    Exec standarddatasetgroom <datasetid>

    which is a stored procedure on the data warehouse DB.  Just make sure you insert the correct datasetid, pulled from the standarddatasetaggregation table.  You can also watch the execution of this in profiler to see what is happening and can go so far as to break down the stored procedure if needed.  If you need help wih doing so (which I would recommend) you can open a call with support.  Also, before making any manual changes against the DB’s, make sure you have a good backup.

  11. Archana says:

    Exec standarddatasetgroom <datasetid> is not working.. Can you please help me ?

  12. savas says:

    Archana, Did u use "datasetid" or you find that id on your db?

  13. Will says:


    You may also check how to send SMS from SCOM 2007 with Ozeki NG SMS Gateway


  14. netar says:

    For some kind of record, such as event, the date is not exactly the date you define, the records are seperated by many tables with suffix enent_XXXXXXXXXXXXXX. there is a field named enddatetime defines when the table can be deleted.