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:
- Start the "System Center Operations Manager Console
- In the "Operations Manager Console" click "Administration"
- In the "Navigation" pane expand "Administration and click "Settings
- In the "Settings" pane, right click "Database Grooming" and the click "Properties"
- In the "Global Management Group Setting – Database Grooming" dialog box, select "Performance Data", and click "Edit"
- In the "Performance Data" dialog box, specify "Older than" days, and the click "Ok". I changed the value into 4
- Repeat step 5 and 6 for the "Event Data"
- 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:
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:
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:
Let’s change the the “EventGroome” Stored procedure from “100” age days to “50” day’s
Update StandardDatasetAggregation set MaxDataAgeDays = 40 where GroomStoredProcedureName = 'EventGroom'
To check if the change is applied run the following sql-query:
SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation where GroomStoredProcedureName = 'EventGroom'
If you want to change other stored procedures you can download the following Script
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.
You can change the Data Retention using the following SQL Query:
UPDATE dtConfig SET Value = number of days to retain data + 1 WHERE Id = 6
More information on grooming can be found here: