Change the Grooming Settings of the SCOM Databases

Standard

In this blog post i ’am going to take you true the steps that are needed for changing the retention time of the databases that are used in System Center Operations Manager.

System Center Operations Manager 2012 makes use off the following Databases:

  • Operational Database (named: OperationsManager)
  • Data Warehouse Database (named: OperationsManagerDW)
  • ACS DataBase

Operational Database (Named: OperationsManager).

The Operational Database is a SQL Server Database that contains all configuration data for the management group  and stores all monitoring data that is collected and processed for the management group. The Operational database retains short-term data, by default 7 day’s.

The Operations Database needs 50% free space at all time. This for growth, and for re-indexing operations to complete successful. When the free space in the Operations Database is going below 40 % System Center Operations Manager will generate a Alert.

I prefer not to increase the the default off 7 day’s and reduce the performance Data and the Event Data to 4 or 5 day’s. When setting up your System Center Operations Manager environment I will set the Database Grooming settings to two day’s to just to reduce the noise that is generated during the setup, configuring and the import off Management Pack’s

Step’s I took to change the Performance Data and the Event Data:

  1. Start the "System Center Operations Manager Console
  2. In the "Operations Manager Console" click "Administration"
  3. In the "Navigation" pane expand "Administration and click "Settings
  4. In the "Settings" pane, right click "Database Grooming" and the click "Properties"
  5. In the "Global Management Group Setting – Database Grooming" dialog box, select "Performance Data", and click "Edit"

    DatabaseRetention

  6. In the "Performance Data" dialog box, specify "Older than" days, and the click "Ok". I changed the value into 4

    PerformanceData4Days

  7. Repeat step 5 and 6 for the "Event Data"
  8. In the "Global Management Group Setting – Database Grooming" dialog box, click "Apply" and click "Ok"

Know that we have changed the database grooming for the OperationsManager database time check if the grooming is working correct.

Grooming off the OperationsManager Database is done by the rule “Partition and Grooming” and runs  once a day at 0:00. The rule is targeted against to the “All management Servers Resource Pool” and is part of the System Center Internal Library. More information on the Partition and Grooming rule can be found here

To check if the Partition and Grooming rule is working you can run the following SQL query:

select * from InternalJobHistory order by InternalJobHistoryId

The Result should look something like this:

SQLQueryResult

Data Warehouse Database (named: OperationsManagerDW)

The data warehouse database is a SQL Server database that stores monitoring and alerting data for historical purposes. Data that is written to the Operations Manager database is also written to the data warehouse database, so reports always contain current data. The data warehouse retains long-term data.

Different data are kept in the data warehouse in unique “Datasets”. A dataset is a set of tables and stored procedures. System Center Operations Managed datasets are used to store historical data. The default dataset consist of the following sets out of the box:

  • Performance Data set
  • State data set
  • Event data set
  • APM data set
  • Alert data set
  • Client Monitoring data set

To get the data sets that are available in your System Center Operations Manager Environment:

select * from Dataset 

The result should look something like this:

selectfromDataset

The most common datasets that we will aggregate are the Performance data set, State data set and Client monitoring data set. We have raw, hourly and daily aggregations.

When you have a default installations of System Center Operations Manager 2012 the data is stored by type, and the ranges from 10 to 400 day’s depending on the data. You can see the grooming setting’s of the OperatinsManagerDW database using the following SQL command:

<pre class="lang:default decode:true " >USE OperationsManagerDW
SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation</pre> 

The result should look something like this:

GroomingConfiguration

Let’s change the the “EventGroome” Stored procedure from “100”  age days to “50” day’s

Use OperationsManagerDW
Update StandardDatasetAggregation set MaxDataAgeDays = 40 where GroomStoredProcedureName = 'EventGroom' 

To check if the change is applied run the following sql-query:

USE OperationsManagerDW
SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation where GroomStoredProcedureName = 'EventGroom'

CheckEventGroomStatus

If you want to change other stored procedures you can download the following Script

ACS Database

When installing ACS into your environment you during the installation process you can set the “Number of days an event is retained in database”. The default configuration is 14 days.

ACSRentetionDefault

You can change the Data Retention using the following SQL Query:

USE OperationsManagerAC
UPDATE dtConfig SET Value = number of days to retain data + 1 WHERE Id = 6

More information on grooming can be found here: