Modifying grooming settings for the OpsMgr databases

OpsMgr 2007 includes three databases: Operational, Data Warehouse, and the ACS database (assuming you have implemented ACS). We discuss how to change the grooming settings for each.

Operations database

The Operational database is the most straightforward of the three, as you can change the settings in the Operations console in the Administration node, under Settings -> General -> Database Grooming. Note that there are multiple data types, each having its own setting. This becomes more significant when we look at grooming the data warehouse.

The default setting for each of the data types is to remove or groom the data after seven days.

Data Warehouse database

The console does not have an interface to modify data retention settings for the data warehouse. You can groom the data warehouse settings by modifying columns in the StandardDatabaseAggregation table in the Data Warehouse database.

By default, data is groomed out at different intervals depending on the degree of aggregation. Data is stored by type, and the ranges for data retention vary from 10 days to 400 days depending on the range of data. You can view the grooming settings by running the following SQL query:

USE OperationsManagerDW

SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation

The default settings returned by this query are displayed in the following table:

To make some sense of this, consider the following:

• The first column (AggregationIntervalDurationMinutes) is the interval in minutes that data is aggregated. NULL is raw data, 60 is hourly, and 1440 is daily. You can see that some performance data is not aggregated at all, some is on an hourly basis, and some is daily.
• MaxDataAgeDays is the maximum number of days data is retained. Depending on the type of data and its degree of aggregation, defaults can range from 10 to 400 days. This is the value we will be modifying, based on the particular type of data (Events, Alerts, State, AEM, or Performance data), and level of aggregation.
• GroomingIntervalMinutes is the grooming process frequency, or how often the groomed stored procedure runs. Performance, Alert, Event, and AEM data is groomed every 240 minutes (4 hours); the procedure to groom State data runs every hour.
• MaxRowtoGroom is how many rows are processed in a given execution of the grooming procedure.

As an example of how this works, let’s look at non-aggregated Event data, which is the first row of information. We know that this pertains to Events information because of the referenced procedure name EventGroom (GroomStoredProcedureName). The query tells us that Event data is not aggregated (AggregationIntervalDurationMinutes=NULL) and is saved for 100 days (MaxDataAgeDays). The EventGroom procedure grooms data (GroomStoredProcedureName), and runs every 240 minutes/4 hours (GroomingIntervalMinutes). Each time the stored procedure runs, it will groom a maximum of 100,000 rows.

The following SQL code changes the grooming frequency for Event data:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘EventGroom’

Changing the retention period for Event data is relatively easy; since it is never aggregated, it only has one row in the StandardDatsetAggregation table. The following syntax can be used as a basis for updating retention periods for other types of data:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘<procedure name>’ AND AggregationIntervalDurationMinutes = ‘<aggregation interval duration>’

Using the above example, let’s look at how to to change the grooming frequency for hourly groomed performance data as an example of how to change retention for a data type that has multiple aggregation levels. We will update the grooming period for Performance data that is aggregated on an hourly basis:

USE OperationsManagerDW

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = <number of days to retain data>

WHERE GroomStoredProcedureName = ‘PerformanceGroom’ AND AggregationIntervalDurationMinutes = ’60’

Be aware that as OpsMgr aggregates most of the data in the Data Warehouse database, its growth on a day-to-day basis is less than the Operational database. However, since the retention period is longer, it will grow to be considerably larger, as you can see from our earlier blog entry, Estimating Database Sizes for OpsMgr 2007 (http://ops-mgr.spaces.live.com/default.aspx?_c01_BlogPart=blogentry&_c=BlogPart&handle=cns!3D3B8489FCAA9B51!171).

*** As an update, if you are running OpsMgr 2007 SP1 and OpsMgr 2007 Reporting SP1, you can use the Data Warehouse Data Retention Policy Tool (dwdatarp.exe) to view and configure data warehouse data retention policies. Instructions for this command line tool and the download are available at http://blogs.technet.com/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx. Thanks to Daniel Savage of the OpsMgr team for providing this! ***

ACS database

Data is groomed out of the ACS Database based on the data retention period specified during setup, with the default being 14 days. After looking at what it takes to groom the data warehouse, ACS is relatively simple! The ACS Collector calls a SQL procedure to remove a partition that is outside of the data retention period. This procedure can be found at %SystemRoot%\system32\security\adtserver\DbDeletePartition.sql. The data retention period itself is specified in the dtConfig table in the OperationsManagerAC database.

To update the data retention period, run the following SQL query:

USE OperationsManagerAC

UPDATE dtConfig

SET Value = <number of days to retain data + 1>

WHERE Id = 6

To retain 7 days of data, set <Value> = 8. Data is accumulated at approximately 7.6 MB per day per workstation.