UIM Database table growing fast after upgrade to UIM 20.3.3/20.4
search cancel

UIM Database table growing fast after upgrade to UIM 20.3.3/20.4

book

Article ID: 233179

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM) Unified Infrastructure Management for Mainframe DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Post-upgrade to 20.3.3/20.4, the DB grew very fast and used up space at an alarming rate. Also profiles are not being deployed/added as expected.

Other symptoms (noticed within MCS):

Issues:

  • No 'Monitored by' probe values displayed
  • 'No profiles added'
  • New servers/device discovered post upgrade.
  • No metrics displaying
  • Some devices are not seen in their group
  • Devices can be in multiple groups but some are missing or show no profiles.

Environment

Release : 20.3

Component : MON_CONFIG_SERVICE

- MCS

- mon_config_service

- UIM 20.3 and 20.4

Cause

- SSRV2AuditTrail is not being purged frequently enough

- SSRV2AuditTrail row count is ~350 million

Resolution

The SSRV2AuditTrail table had grown dramatically after an upgrade.
 
This was a known issue prior to 20.4. It is fixed in the mon_config_service probe as of UIM v20.4.

As of UIM 20.4 this should no longer be an issue.
***Resolved an issue where MCS was not maintaining the SSRV2AuditTrail database table. This was increasing the table size.***

Please refer to the MCS (mon_config_service) release notes and more audit and garbage configuration settings below for future reference/more details:
 
 
In the meantime, you can truncate the related tables to alleviate the problem quickly.
 
Here is a good approach to follow to resolve this problem:
 
BEGIN TRANSACTION;
TRUNCATE TABLE SSRV2AuditTrailModification;
ALTER TABLE SSRV2AuditTrailModification DROP CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail;
TRUNCATE TABLE SSRV2AuditTrail;
ALTER TABLE SSRV2AuditTrailModification WITH NOCHECK ADD  CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail FOREIGN KEY(auditrecord)
REFERENCES SSRV2AuditTrail(id);
ALTER TABLE SSRV2AuditTrailModification NOCHECK CONSTRAINT FK_SSRV2AuditTrailModification_SSRV2AuditTrail;
COMMIT;

 

Related parameters in the mon_config_service.cfg

garbage_cleanup_interval_minutes by default is set to 20

garbage_cleanup_batch_size is set to 10000

days_in_audit_trail_history default is 30     #as of 20.4

To keep the table smaller, set the days_in_audit_trail_history to a lesser value, e.g., 1, 3, or maybe 5 so the audit trail table does not become so large. Keep track of it each day to decide on the value you should set it to.

If you have a large number of records in the SSRV2Audttrail and SSRV2AudittrailModification tables that you want to clean up, decrease the cleanup interval minutes and decrease the batch size rather than increase the batch size.

Check and/or keep track of the row count by running the query:

   select count(*) from SSRV2AuditTrail

In this case, it was originally 350M rows.

Run the stored procedure-> exec sp_spaceused in SQL Server

database size from exec sp_spaceused; was: 386454.81 MB or 386GB, unallocated ~133MB

After about 15-20 minutes or so the SSRV2AuditTrail was much smaller after truncating the tables as described above.

Run the stored procedure-> exec sp_spaceused in SQL Server to check again.

Cold start the mon_config_service probe (Deactivate-Activate).

Then check the mon_config_service.log for successes/failures.

Additional Information

Cross reference for a related case:
 
Case 32997010 MCS Profiles status stay in Modified status indefinitely
 
- UIM 20.33
- MCS HF7
- Enhanced Profiles
 
Dev/Eng identified a scenario where robots which were moved from one hub to another hub, were still holding references to the OLD hub address in some data structures. Therefore, some callbacks to robot hub were failing and being retried. During this process, the robot's alive_time was getting updated by means of the latest robot address in other threads.
 
Symptoms:
MCS Profiles status stay in Modified status indefinitely
ssrv2profile profile also reflects the profile in Modified status but same status remains.
Devices' alive time is continuously changing.
 
The hotfix for the issue is also attached to this KB Article:
discovery_server 20.41HF1

Attachments

discovery_server-20.41-HF1_1647367574091.zip get_app