Snmpcollector is creating a lot of metric data in the CA_UIM database

Document ID : KB000046713
Last Modified Date : 14/02/2018
Show Technical Document Details

 

Issue: 

 

CA_UIM database is filling up at an alarming rate after installing snmpcollector 3.2

 

Environment:  

 

Unified Infrastructure Management (UIM) 8.4 and above

Snmpcollector 3.2 and above

SQL Server

 

Background: 

 

UIM and Snmpcollector have numerous options in how to manage the incredible amount of data that it is able to collect.

 

 

Instructions:

 

Validate that Snmpcollector is the issue:

Within Infrastructure Manager, Navigate to the Data_Engine and click the "Status Tab".

Sort by "KB Raw Data".

Keep this Window open as we continue to the next step - we will use this data to cross check what each snmpcollector is publishing.

Validate what data is currently configured for polling in snmpcollector:

It is often the case where metric families that were thought to be off have been turned on by a template to verify what snmpcollector is publishing navigate to:

http://localhost:9715/metrics Change localhost to where the snmpcollector probe is installed.

Select "Enabled Metrics" this will list all enabled metrics for this snmpcollector.

Compare the data_engine table to what is being stated in the snmpcollector probe to see if extra metrics are turned on.

If erroneous metrics are turned on disable these metrics to reduce database disk consumption.

Change QoS retention periods:

If the minimum amount of metrics are selected but we wish to further reduce database size we can edit specific QoS retention periods.

To do so, open up Data_Engine within Infrastructure Manager.

Navigate to the Quality of Service tab.

Open the QoS Name - a new window should open up.

Here, we can modify the "Delete raw data older than" to be a shorter period than what is common for other probes. Many customers Override with 30 days.

This will delete raw data that is older than 30 days, significantly reducing database disk usage.

Validating current database space:

By default in Microsoft SQL Server deleting data will not reduce the actual disk space on hard disk. The .mdf file will not shrink by default.

To verify that the database has reduced its data usage run the below query replacing DATABASE_NAME with the correct name (CA_UIM):

USE [DATABASE_NAME]

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

CA Support does not recommend shrinking the .mdf file as this is an expensive and usually futile operation. The .mdf will not expand until the above data is used up.