How to trouble shoot data retention and Database growth

Document ID : KB000009547
Last Modified Date : 27/07/2018
Show Technical Document Details

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:

KB000033680  : CA UIM (Nimsoft) Database Best Practices for MS SQL Server
(previously published as TEC000003224)

Make sure you have followed all of the suggestions there.
This should solve most issues moving forward.

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.


1. First execute the following query which will tell you how old the oldest sample in the DB is:

select min(sampletime) from rn_qos_data_0001;

Find out how many days ago this is (for example suppose it is 400 days).

2. 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.

3. Deactivate the data_engine

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

5. Issue this query:

truncate table tbnlogging;

6. activate data_engine

7. use the probe utility to execute run_admin_now

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

9. More importantly you can check SELECT * FROM TBNLOGGING; and it will have some information 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.