Setting the SCOM Data Warehouse Retention Policies

How long do we keep data for in the Data Warehouse and how can I change the values? That’s probably a common question, so if you don’t know you can use a couple of methods.


Option 1: Using SQL

Firstly, we can perform a couple of simple SQL Queries against the OperationsManagerDW Database.




Take note of the GUID’s returned in the DatasetId column and align this with the DatasetDefaultName. So in the above image you can see that the “Alert Data Set” has the GUID starting with “FOC75C2E”.


Now query the dbo.StandardDatasetAggregation Table.







Locate the DatasetID using the GUID you identified in the previous step.

You’ll see the AggregationTypeId Column will contain one of the following values which will tell you what type of data we’re collecting:

0 = Raw (Non-Aggregated Data)
10 = Sub-Hourly
20 = Hourly
30 = Daily


From here you can set the “MaxDataAgeDays” value to whatever you want it to be.

So using our example the GUID starting with “FOC75C2E” we can identify it as being the Alert Data Set with a retention period of 400 days for RAW Data.


Option 2: dwdatarp

Another option (and probably the easier one if you’re unfamiliar with using SQL) is to use the “dwdatarp.exe” tool… (Microsoft Operations Manager Data Warehouse Retention Policy Utility – dwdatarp)

Its pretty simple to use, just run it from a command prompt or a PowerShell window without any parameters and it’ll tell you what to do.














So to find out what your data retention policies are, run it with the -s and -d parameters.

dwdatarp -s servername -d OperationsManagerDW

And you’ll get back the results like this…










So here we can see the same data that our SQL Query returned and right at the top is the Alert Data Set with a retention period of 400 days for RAW Data.


If you want to change anything, then you can use the -ds -a and -m parameters.
So for example if you want to change the Performance Raw Data retention to 5 days you’d do that like this:

dwdatarp -s servername -d OperationsManagerDW -ds Performance -a Raw -m 5

Obviously you’ll need to have appropriate permissions to make the change, but the change will be immediate.




Thanks for your posting.

After changing the retention period of hourly performance data set from 400 to 100 days, how long will it take to groom and free up space from DW ???

In case if it doesn’t happens quickly, then how I can force it to purge the old data ?

The grooming intervals for the Data Warehouse are stored in the StandardDatasetAggregation table in the OperationsManagerDW Database. Look at the values in GroomingIntervalMinutes.

Oh my goodness! Incredible article dude! Thank you so much, However
I am encountering problems with your RSS. I don’t know the
reason why I am unable to subscribe to it. Is there anyone else having the same RSS issues?
Anyone that knows the solution can you kindly respond? Thanks!!

Thanks for the feedback. I’m now on twitter @TimCulham, I’ll announce posts there if it helps 🙂

Leave a Reply