CA UIM database has grown by 30 GB in a single day

Products

• DX Infrastructure Management
• NIMSOFT PROBES

Issue/Introduction

SQL Server Transaction logs growing rapidly (30 GB in a single day)

 

 

 

Cause

The transaction log file is maintained by Microsoft to enable a rollback of the database should a major database failure occur.

Environment

UIM 9.02 and later
DB - MS SQL 2012

Resolution

Follow the below step:-

Set the UIM database Recovery model to Simple

Deactivate the NAS and Data_engine probe.
Modify the NAS.cfg file to reduce the retention period :-

transactionlog_compress = 1
transactionlog_age = 1
transactionlog_summary = 1

Modify the data_engine.cfg to delete historical Data older than from 72 days to 10 days

Activate the NAS and Data_engine probe.

Additional Information

If you cannot use Simple recovery model you will need to make sure your backup process is check pointing the log file and clearing transactions. If you only backup once a day the log file will grow all day long and then be cleared during the backup process. Keep this in mind as you see that file increase in size.