Large .LDF Transaction log file (Symantec_CMDB_Log.ldf)

Products

• IT Management Suite

Issue/Introduction

The SMP (Symantec Management Platform) server is running slowly and the Symantec_CMDB_Log.ldf file is larger than 500 MB each.

Environment

ITMS 7.x, 8.x

Cause

The .LDF (Transaction Log) file is a temporary file that assists in database restoration. It tracks all changes made to a database since the last backup so that if the backup is restored, the .LDF file can be used to restore those changes that were made after the backup was taken. Normal committed transactions are cleared out of this file after a backup is made of those transactions (occurs as part of either a full or incremental backup of the database).  Databases in simple recovery mode will normally clear committed transactions at checkpoints.  Clearing transactions does not change the size of the transaction log but allows new transactions to reuse the cleared space.

If the number of transactions within the transaction log gets too large before getting cleared out, sometimes SQL does not clear them.  After that transaction logs will continue to grow even after normal backups are attempted.   This has been seen with customers that use Full or Bulk recovery modes without having regular incremental backups OR in simple recovery a large number of transactions stack up before the next checkpoint occurs.

Resolution

If this file grows to be over 500 MB, it is possible that it is not being truncated during your backups. Please make sure that you are doing regular backups using the Database Maintenance Planner or using a special SQL plug-in for your backup software. Do not simply back up the .MDF file (Symantec_CMDB_Dat.mdf) for the database because not all of the required SQL data is in the .MDF file as much of SQL remains in memory until it can be committed to the .MDF file.  Additionally the .MDF and .LDF files must be in synch (only occurs when a database is shut down or is detached) otherwise the .MDF cannot be restored by itself.

To alleviate the problem while you fix your backup settings, please verify Microsoft documentation in how to address large transaction logs:

https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15