Large .LDF Transaction log file

Products

• IT Management Suite

Issue/Introduction

Server is running slowly and the Altiris_log.ldf, Altiris_Incidents_log.ldf, and/or eXpress_log.ldf file are larger than 100 MB each.

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 normal clear committed transactions at check point.  Clearing transactions does not change the size of the transaction log, but allows new transactions to reuse the cleared space.

If the number transaction within the transaction log get to 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 100 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 backup the .MDF file 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, you can manually do a onetime truncation of  the .LDF file. To manually truncate the .LDF file, follow these steps:

1. Make sure you have backed up the respective database for the .LDF file you need to truncate.
2. First try and truncate the transaction log through SQL commands
3. Open SQL Server Enterprise Manager
4. Start a new Query command
5. The following SQL command will manually clear the transaction logs for the Altiris database. 
   use Altiris
   Backup Log Altiris with truncate_only
   DBCC Shrinkfile('Altiris_Log',truncateonly)
   
   NOTE: The same commands can be used for other database used by Altiris products, just substitute out "Altiris" and "Altiris_log" for the database and database log names used in the other Altiris pattern taking note to change the names appropriately depending on the database you are trying clean up for. 
6. Wait 5 min and repeat the same command at least once more
7. Make another separate FULL backup of the database

 

If the above does not shrink the log file as expected, a more aggressive and invasive option can be used if necessary. 

1. For Altiris_log.ldf or Altiris_Incidents_log.ldf, go to Services Manager on the Notification Server and stop these services in this order:
   1. Altiris Client Message Dispatcher
   2. Altiris NS Receiver
   3. Altiris Service
   4. World Wide Web Publishing Service
2. If eXpress_log.ldf, go to Services Manager on the Deployment Server and stop these services in this order:
   1. Altiris eXpress PXE Config
   2. Altiris eXpress PXE MTFTP
   3. Altiris eXpress PXE Server
   4. Altiris eXpress Server
   5. Altiris Deployment Server DB Management
   6. Altiris Deployment Server Console Manager
   7. Altiris Deployment Server Data Manager
3. Stop any other services on your Notification Server and/or Deployment Server that begin with Altiris.
4. Open SQL Server Enterprise Manager.
5. Browse to your SQL Server, and then Databases.
6. For each respective database for the .LDF file you want to truncate, do the following:
   1. Right-click the database and choose All Tasks > Detach Database.
   2. If any connections are using the database, click Clear, OK, and then No.
   3. Click OK and OK again.
7. In Windows Explorer, delete each .LDF file you want to truncate.
8. Reopen SQL Server Enterprise Manager.
9. For each database you detached in step 7, do the following:
   1. Right-click Databases choose All Tasks > Attach Database.
   2. Select the .MDF file for the database
   3. Click OK, Yes, and then OK.
   4. Right-click the newly attached database and choose Properties.
   5. Go to the Options tab, set the Recovery Model to Simple, and click OK.
10. If you truncated Altiris_log.ldf or Altiris_Incidents_log.ldf, go to Services Manager on the Notification Server and start these services in this order:
    1. Altiris Service
    2. Altiris NS Receiver
    3. Altiris Client Message Dispatcher
11. If you truncated eXpress_log.ldf, go to Services Manager on the Deployment Server and start these services in this order:
    1. Altiris Deployment Server Data Manager
    2. Altiris Deployment Server Console Manager
    3. Altiris Deployment Server DB Management
    4. Altiris eXpress Server
    5. Altiris eXpress PXE Server
    6. Altiris eXpress PXE MTFTP
    7. Altiris eXpress PXE Config
12. Start any other services on your Notification Server and/or Deployment Server that begin with Altiris.

Applies To
SQL Server with Altiris Notification Server or Deployment Server.