Extra, Extra, read all about it! Updated 24 June 2022
The DWdataRP utility works on SCOM2016+
This tool has been around since SCOM 2007, and blogged about by Kevin Holman and the SCOM Engineering Team
DW Retention https://kevinholman.com/2010/01/05/understanding-and-modifying-data-warehouse-retention-and-grooming/
Kevin added these –
Here is a link to the original command line tool, DWDATARP: https://kevinholman.com/files/dwdatarp.zip
Here is a tool which makes this even simpler: https://www.scom2k7.com/scom-datawarehouse-grooming-settings-super-easy-with-new-gui-tool/
And the latest new tool which adds features and simplicity, which is what I recommend: https://blakedrumm.com/blog/scom-dw-grooming-tool/
Download the link at the second link, and save to your machine (x86 or x64)
Run the executable
dwdatarp.exe -s <DB Server Name> -D <DW Database Name>
Example dwdatarp.exe -s 16DB02 -D OperationsManagerDW
Set Data sets according to SLA
Ever need to shorten up your Lab DB, so you can export it, to leverage PowerBI dashboards?
Example Output
PS C:\Users\sqladmin\Documents\DWDataRP\amd64> .\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW
Dataset name Aggregation name Max Age Current Size, Kb
—————————— ——————– ——- ——————–
Alert data set Raw data 180 28,736 ( 0%)
Client Monitoring data set Raw data 30 0 ( 0%)
Client Monitoring data set Daily aggregations 400 136 ( 0%)
Configuration dataset Raw data 400 259,968 ( 3%)
DPM event dataset Raw data 400 0 ( 0%)
DPM.Backup.DataSet Raw data 400 0 ( 0%)
DPM.Backup.DataSet Hourly aggregations 3 0 ( 0%)
DPM.Backup.DataSet Daily aggregations 182 0 ( 0%)
DPM.DiskMgmt.DataSet Raw data 400 0 ( 0%)
DPM.DiskMgmt.DataSet Hourly aggregations 3 0 ( 0%)
DPM.DiskMgmt.DataSet Daily aggregations 182 0 ( 0%)
DPM.DiskUtilization.DataSet Raw data 400 0 ( 0%)
DPM.DiskUtilization.DataSet Hourly aggregations 3 0 ( 0%)
DPM.DiskUtilization.DataSet Daily aggregations 182 0 ( 0%)
DPM.Recovery.DataSet Raw data 400 0 ( 0%)
DPM.Recovery.DataSet Hourly aggregations 3 0 ( 0%)
DPM.Recovery.DataSet Daily aggregations 182 0 ( 0%)
DPM.SLATrend.DataSet Raw data 400 0 ( 0%)
DPM.SLATrend.DataSet Hourly aggregations 3 0 ( 0%)
DPM.SLATrend.DataSet Daily aggregations 182 0 ( 0%)
DPM.TapeUtilization.DataSet Raw data 400 0 ( 0%)
DPM.TapeUtilization.DataSet Hourly aggregations 3 0 ( 0%)
DPM.TapeUtilization.DataSet Daily aggregations 182 0 ( 0%)
Event data set Raw data 100 456,144 ( 5%)
Exchange 2013: Mailbox Database data warehouse dataset Raw data 30 0 ( 0%)
Exchange 2013: Mailbox statistics data warehouse dataset Raw data 30 0 ( 0%)
Exchange 2013: Mailbox statistics data warehouse dataset Daily aggregations 400 0 ( 0%)
Performance data set Raw data 10 216,096 ( 2%)
Performance data set Hourly aggregations 400 5,552,832 ( 61%)
Performance data set Daily aggregations 400 221,312 ( 2%)
Process Monitoring: Performance Metric State data warehouse dataset Raw data 10 0 ( 0%)
Process Monitoring: Performance Metric State data warehouse dataset Hourly aggregations 90 0 ( 0%)
Process Monitoring: Performance Metric State data warehouse dataset Daily aggregations 180 0 ( 0%)
Process Monitoring: Process Health State data warehouse dataset Raw data 10 0 ( 0%)
Process Monitoring: Process Network Ports data warehouse dataset Raw data 10 0 ( 0%)
State data set Raw data 180 6,080 ( 0%)
State data set Hourly aggregations 400 2,335,968 ( 25%)
State data set Daily aggregations 400 99,840 ( 1%)
Commands to run to clean up the warehouse…
.\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds Performance -a Hourly -m 60
.\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds Performance -a Hourly -m 60
.\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds State -a Hourly -m 60
.\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds event -a raw -m 30
Example Output
PS C:\Users\admin\Documents\DWDataRP\amd64> .\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds Performan
ce -a Hourly -m 60
Max data age set to 60 on dataset “Performance data set” aggregation type “Hourly aggregations”
PS C:\Users\admin\Documents\DWDataRP\amd64> .\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds State -a
Hourly -m 60
Max data age set to 60 on dataset “State data set” aggregation type “Hourly aggregations”
PS C:\Users\admin\Documents\DWDataRP\amd64> .\dwdatarp.exe -s 16db02.testlab.net -d OperationsManagerDW -ds event -a
raw -m 30
Max data age set to 30 on dataset “Event data set” aggregation type “Raw data”
Don’t forget to execute cleanup
–This will manually run grooming for this dataset
DECLARE @DatasetId uniqueidentifier
SET @DatasetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = ‘Alert’)
EXEC StandardDatasetGroom @DatasetId
–This will manually run grooming for this dataset
DECLARE @DatasetId uniqueidentifier
SET @DatasetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = ‘Event’)
EXEC StandardDatasetGroom @DatasetId
–This will manually run grooming for this dataset
DECLARE @DataSetId uniqueidentifier
SET @DataSetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = ‘Perf’)
EXEC StandardDatasetGroom @DataSetId
–This will manually run grooming for this dataset
DECLARE @DataSetId uniqueidentifier
SET @DataSetId = (SELECT DatasetId FROM StandardDataset WHERE SchemaName = ‘State’)
EXEC StandardDatasetGroom @DataSetId