By a customer, the SCOM’s Database OperationManagerDW growth fast and becomes very big.

To see what’s happens, in SSMS (SQL Server Management Studio), I right-click on the database open Reports>Standard Report> Disk Usage by Top Tables:
You have many interesting reports with SSMS and I advise you to use it.
In my case the “Disk Usage by Top Tables” report displays me many perf.perfHourly and State.StateHourly tables with a _guid():

After reading some blog’s, the most interesting blog was the blog from Kevin Holman about “Understanding and modifying Data Warehouse retention and grooming”
My goal is just to share, how I use this good blog to help my customer.
Using the query to retrieve the information like this one below, I discuss with the SCOM responsible:

1
SELECT DataSetDefaultName, AggregationTypeId, MaxDataAgeDays FROM StandardDatasetAggregation sda INNER JOIN dataset ds ON ds.datasetid = sda.datasetid ORDER BY DataSetDefaultName


According to the Scom Application owner, we apply some recommendations.
The first settings was apply by the SCOM responsible on the SCOM server side:

1
2
3
4
5
6
7
8
9
10
11
12
13
dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "Performance data set" -a "Hourly aggregations" -m 60
 
dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "Performance data set" -a "Daily aggregations" -m 180
 
dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "Alert data set" -a "Raw data" -m 100
 
dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "Event data set" -a "Raw Data" -m 10
 
dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "State data set" -a "Raw data" -m 60
 
dwdatarp.exe -s <sqlservername>  -d OperationsManagerDW -ds "State data set" -a "Hourly aggregations" -m 60
 
dwdatarp.exe -s <sqlservername> -d OperationsManagerDW -ds "State data set" -a "Daily aggregations" -m 90

In a second time,  I run these queries to change some retentions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
--Alert Data
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 100 --default is 400
 
WHERE GroomStoredProcedureName = 'AlertGroom'
 
--Event Data
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 10 --default is 100
 
WHERE GroomStoredProcedureName = 'EventGroom'
 
--Performance HOURLY
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 60 --default is 400
 
WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationTypeId = '20'
 
--Performance DAILY
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 180 --default is 400
 
WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationTypeId = '30'
 
--State RAW
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 60 --default is 180
 
WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '0'
 
--State HOURLY
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 60 --default is 400
 
WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '20'
 
--State DAILY
 
UPDATE StandardDatasetAggregation
 
SET MaxDataAgeDays = 90 --default is 400
 
WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationTypeId = '30'

After these changes, the database wasn’t growing fast and no more so many perf.perfHourly and State.StateHourly tables with a _guid().

I do this blog to share my experience with the SCOM database OperationManagerDW and give you some tips to have an optimized database for SCOM.

Thanks  to Kevin Holman for your help!

I hope this blog can help other DBA to do the same to not have a big database for nothing… 😉

 

Post Views: 1,965
Thumbnail [60x60]
by
Stéphane Haby