How to troubleshoot data retention and control database growth
search cancel

How to troubleshoot data retention and control database growth

book

Article ID: 9547

calendar_today

Updated On:

Products

CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM) DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

  • Sometimes client databases start growing unexpectedly.
  • Some times this is related to new probes being added or the addition of new monitoring.
  • At other times this is a symptom of a problem on the back end database with data maintenance.
  • The first thing to do is always go through the article named:

    DX UIM (Nimsoft) Database Best Practices for MS SQL Server

    Should you need to dive deeper into why your data is not being truncated the steps listed below should help you find the problem and resolve it.

Environment

Release: UIM, any version
Component: data_engine

Resolution

Before taking any of the below steps, we advise that you work with you database administrator.

 

1. First check your database and find out which RN table is the highest-numbered RN table in your environment; these tables are in the format RN_QOS_DATA_0001, RN_QOS_DATA_0002 etc. and you will need to identify the highest number in your particular database as this will vary.

2. Execute the following queries which will tell you how old the oldest sample in the DB is:

    select min(sampletime) from rn_qos_data_0001;

    select min(sampletime) from rn_qos_data_xxxx; -- the highest-numbered RN table goes here.

One of these days may be older or they may be the same; whichever one is older (if applicable), find out how many days ago that date is (for example suppose it is 400 days).

3. Set the data_engine retention settings to approx 1 week less than this, so for example if the oldest sample is 400 days old, set 'delete raw samples after' to 393 days. Then set "historic data" retention to one day higher, e.g. 394 days. This will cause the data_engine maintenance to only need to delete 1 week of data.

4. Deactivate the data_engine

5. Using Raw configure mode for the data_engine probe, set loglevel to 5, set logsize to 100000, and table_maintenance_loglevel to 5

6. Issue this query:

    truncate table tbnlogging;

7. activate data_engine

8. use the probe utility to execute run_admin_now

9. You should see some lines in the logs which say "ExecuteSP" or spn_de_DataMaint which indicate what maintenance is doing

10. More importantly you can check SELECT * FROM TBNLOGGING; it will have some info which is logged by the data_engine about the stored procs that run.

      Look for non-zero numbers in the 'RC' column which indicate a failure.

Note: if this succeeds, than adjust the data_engine parameters further (retention days) and run maintenance incrementally until you are "caught up" then you can set the retention to what you want.