LiquidObject

FreeNAS 8.3 and VMware ESXi’s VMXNet3 adapter »« Counter Intelligence via Palo Alto Networks

SCOM 2012 database grooming

Approximately three months back we migrated to SCOM 2012 and have been slowly rebuilding our configuration. In defining the configuration we forgot one key part, database grooming customization. By default some data is kept for a couple of days but a lot of day is kept for either 180 days or 400 days. While in a lab environment this may be ok since you are only monitoring a few systems in production this will cause some unexpected database growth issues. Below you can see the defaults configured.

 
Dataset name                   Aggregation name     Max Age     Current Size, Kb
—————————— ——————– ——- ——————–
Alert data set                 Raw data                 400         8,440 (  0%)
Client Monitoring data set     Raw data                  30             0 (  0%)
Client Monitoring data set     Daily aggregations       400            16 (  0%)
Configuration dataset          Raw data                 400       133,616 (  0%)
DPM event dataset              Raw data                 400             0 (  0%)
Event data set                 Raw data                 100       594,592 (  2%)
Microsoft.Exchange.2010.Dataset.AlertImpact Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Hourly aggregations        3             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Daily aggregations       182             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Raw data                 400            16 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Daily aggregations       400             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Daily aggregations       400             0 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Raw data                   3        17,424 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Hourly aggregations        7       225,104 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Daily aggregations       182       104,592 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Raw data                   7         1,616 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Hourly aggregations       31         6,480 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Daily aggregations       182           688 (  0%)
Performance data set           Raw data                  10     4,984,944 ( 13%)
Performance data set           Hourly aggregations      400    26,558,360 ( 69%)
Performance data set           Daily aggregations       400     3,047,320 (  8%)
State data set                 Raw data                 180        37,280 (  0%)
State data set                 Hourly aggregations      400     2,481,936 (  6%)
State data set                 Daily aggregations       400       117,280 (  0%)
 

To prevent the database from growing to hundreds of GB we need to adjust the retention policies. In order to accomplish this we need to download the dwdataarp.exe utility from Microsoft at: http://blogs.technet.com/b/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx

With this installed open up an administrative command line on the SCOM server we can begin.

First run: dwdatarp.exe -s localhost -d “OperationsManagerDW”
This will show you your current configuration, now we need to tweak some of the retentions. The below example is a mix of retention periods in an environment with Exchange 2010 and DPM 2012 installed.

dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Alert data set" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Event data set" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Client Monitoring data set" -a "Daily aggregations" -m "60"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Configuration dataset" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "DPM event dataset" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.Availability" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.Availability" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.TenantMapping" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performancedata set" -a "Raw data" -m "7"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performance data set" -a "Hourly aggregations" -m "14"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performance data set" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Raw data" -m "7"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Hourly aggregations" -m "14"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Daily aggregations" -m "90"

 

 

After making the change and waiting for the automated grooming to complete I ended up dropping the database size from 42GB and growing to 21GB.

 

Dataset name                   Aggregation name     Max Age     Current Size, Kb
—————————— ——————– ——- ——————–
Alert data set                 Raw data                  30         4,656 (  0%)
Client Monitoring data set     Raw data                  30             0 (  0%)
Client Monitoring data set     Daily aggregations        60            16 (  0%)
Configuration dataset          Raw data                  30       133,552 (  1%)
DPM event dataset              Raw data                  30             0 (  0%)
Event data set                 Raw data                  30       352,040 (  2%)
Microsoft.Exchange.2010.Dataset.AlertImpact Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Hourly aggregations        3             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Daily aggregations       182             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Raw data                  30            16 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Daily aggregations        90             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Daily aggregations        90             0 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Raw data                   3        17,680 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Hourly aggregations        7       226,384 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Daily aggregations        90       104,144 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Raw data                   7         1,616 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Hourly aggregations       31         6,416 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Daily aggregations        90           688 (  0%)
Performance data set           Raw data                  10     5,047,512 ( 30%)
Performance data set           Hourly aggregations       14     6,600,016 ( 39%)
Performance data set           Daily aggregations        90     3,047,104 ( 18%)
State data set                 Raw data                   7        23,840 (  0%)
State data set                 Hourly aggregations       14     1,064,864 (  6%)
State data set                 Daily aggregations        90       117,088 (  1%)

 

Also, if you want to speedup the time it takes for the cleanup to occur from within SQL you can run the following command to reduce the time period between cleanup.

update StandardDatasetAggregation set GroomingIntervalMinutes = '11' where GroomingIntervalMinutes = '240'

After cleanup has finished, run the below to change the configuration back to what it was

update StandardDatasetAggregation set GroomingIntervalMinutes = '240' where GroomingIntervalMinutes = '11'
March 11, 2013 at 11:44 am
Leave a Reply or trackback