SystemCenter

All about System Center Articles and Tutorials

Modify SCOM Data Warehouse Retention Policy

Posted by on Aug 16, 2016 in SCOM 2012, SQL

Modify SCOM Data Warehouse Retention Policy

One of my clients wanted to know the default SCOM Data Warehouse data retention policy and wanted to modify the same according to their SCOM environment.

A Data Warehouse is often one of the largest databases supported by an organization.  Large databases cost money and time to support.  They are difficult to maintain and costs more to backup. They will take much more time to restore in case of a disaster. The larger they get, can impact how long the reports take to complete.

So, rather than keeping everything as per default values, it’s always advisable to adjust the SCOM Data Warehouse retention policy according to your environment.

There are two ways to check and modify the OpsMgr DW retention policy:

1: dwdatarp.exe
2: Using SQL

dwdatarp.exe 

Data Warehouse Data Retention Policy tool is the safest and easiest way for those who are not familiar using SQL. 

The tool can be downloaded from here: dwdatarp.exe 

The server on which dwdatarp.exe is running must have access to the OperationsManagerDW along with the user running the tool. 

It’s pretty simple to use. Just run it from a command prompt or a PowerShell and choose the parameters as per your requirement.

1

To check the DW retention policies, run the command with –s and –d parameters.

Example:

dwdatarp.exe -s SERVERNAME -d OperationsManagerDW

2

Here I have already modified most of the default values from 400 to 365 days.

If you want to modify the values you can use –ds –a and –m parameters.

Example:

Dwdatarp.exe -s SERVERNAME -d OperationsManagerDW – ds “Performance data set” –a “Hourly aggregations” –m 365

The modification will replicate immediately and data will be groomed during next grooming workflow. 

USING SQL

Here we should be aware of the correct tables on OpsMgr DW.

We need to look at the StandardDatasetAggregation table.

1: Login on to OpsMgr DW server.
2: Open SSMS and execute below command:

use OperationsManagerDW
go
SELECT ds.DatasetId,DataSetDefaultName,
AggregationTypeId,
MaxDataAgeDays
FROM StandardDatasetAggregation sda
INNER JOIN dataset ds on ds.datasetid = sda.datasetid
ORDER BY DataSetDefaultName

3

The query will give us following required parameters:

  • Dataset Id (GUID)
  • AggregationTypeId – The Aggregation Type
  • MaxDataAgeDays – The current maximum retention setting.

The AggregationTypeId values represents below:

0 = Raw
20 = Hourly
30 = Daily 

So, here we want to modify the retention policy for below highlighted Data Set.

4

Below is the query which will modify the retention policy for above highlighted Data Set from 60 to 45 days: 

use OperationsManagerDW
go
update StandardDatasetAggregation set MaxDataAgeDays = 45
where DatasetId=’C5242C68-BA55-4B75-8FBE-24BD9129874F’
and AggregationTypeId=30

5

If we check the OpsMgr DW retention policy again, we can see the changes are done and data will be groomed during next grooming workflow.

6

You can select the appropriate option to modify the OpsMgr DW retention policy accordingly.

If you want more information on this you can refer below wonderful blog written by Kevin Holman:

Understanding and modifying Data Warehouse retention and grooming

Hope it will help.

Submit a Comment

Your email address will not be published. Required fields are marked *