Database grooming-- the ops manager datawarehouse is growing over 1 GB every two days. Which parameter can I adjust to better control the size of the database?

Question

Database grooming-- the ops manager datawarehouse is growing over 1 GB every two days.   Which parameter can I adjust to better control the size of the database?  I have approxiamtely 220 GB of free space currently.

 

C:\x86>dwdatarp.exe -s "scom sql server" -d "operationsmanagerdw"
Dataset name                   Aggregation name     Max Age     Current Size, Kb

------------------------------ -------------------- ------- --------------------

Alert data set                 Raw data                 400       100,696 (  0%)

Client Monitoring data set     Raw data                  30             0 (  0%)

Client Monitoring data set     Daily aggregations       400            16 (  0%)

Configuration dataset          Raw data                 400     2,338,984 (  6%)

Event data set                 Raw data                 100    19,211,616 ( 49%)

Microsoft.Exchange.2010.Dataset.AlertImpact Raw data                   7
   160 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Hourly aggregations        3
 1,168 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Daily aggregations       182
 3,920 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Raw data                 40
0           864 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Daily aggregations       40
0             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Raw data
 7             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Daily aggregations       4
00             0 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Raw
data                   3        13,712 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Hour
ly aggregations        7       109,008 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Dail
y aggregations       182        71,288 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Raw data
                   7         6,288 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Hourly a
ggregations       31        19,088 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Daily ag
gregations       182         2,240 (  0%)
Performance data set           Raw data                  10     3,252,864 (  8%)

Performance data set           Hourly aggregations      400    10,620,832 ( 27%)

Performance data set           Daily aggregations       400       537,896 (  1%)

State data set                 Raw data                 180       141,536 (  0%)

State data set                 Hourly aggregations      400     2,781,344 (  7%)

State data set                 Daily aggregations       400       280,200 (  1%)

---

dsk

Answer 1

Hi Kimdav,

Had you checked whether transaction data or log growing so largely? If it is log growing, you could adjust the database recovery model. Could you please elaborate your requirement in more?

thanks,
Jerry

Answer 2

It is the database which is growing which I need to control.  I have no issue with the transaction logs at this point.

---

dsk

Answer 3

The database is growing at approximately 1.3 GB every two days which is way too much. (keep in mind we also are taking SQL backups on this same drive so an increase in database size actually decreases free space by twice as much.)  We only need maybe a few months worth of data for reports.

Which database grooming parameter must I change to keep the size of this database constant.

---

dsk

Answer 4

Do you check which table contribute the most to the growth? Most likely the growth is contributed by system log tables, then you need to find a way to archive and reduce the size periodically.

Answer 5

Can you provide more steps on how to check which table is causing the most growth? 

How would I reduce this archive automatically or is this an manual process.  Either way do you have steps on how to do this?

I am not a DBA and do not normally work on SQL.

Thanks.

 

---

dsk

Answer 6

Hi,
you can rightclick on the DB name in SSMS,

Select Reports -> Standard Reports -> Disk Usage by Top Tables
It should tell you which table occupied the most disk space.

As for archiving the data, it is based on your business decision which is manual process as well.

Answer 7

Event.EventDetail_XXZZZXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX             22,850,512 KB

Event/Event Paramenter_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  3,577,600 KB

dbo.ManagedEntityProperty                                                                                    2,904,136 KB

 

I am unfamiliar with what theTable Entries represent?  Can you tell me the significance of these entries above.

How would I decrease the size manually??

---

dsk