A Brief Introduction to SQL Server Transaction Log Files

TLDR

• The transaction log file (.ldf) is responsible for recording all data changes, ensuring data consistency and integrity.
• The recovery model determines the truncation mechanism and restore capabilities of the transaction log: the Simple model truncates automatically, while the Full and Bulk-Logged models require transaction log backups to truncate.
• Transaction log backups are key to preventing unlimited log growth and can only be performed after a full backup has been executed.
• Before shrinking file space, a transaction log backup should be performed; otherwise, space cannot be effectively released.
• If the Full recovery model is selected but transaction log backups are not performed, the log will continue to expand and exhaust disk space.

Recovery Models and Transaction Log Truncation

In SQL Server, the transaction log file (.ldf) is used to record all data changes. When a Checkpoint is triggered, the system writes committed changes to the primary database file (.mdf). Transaction log truncation is the process of marking committed transactions as reusable; if not truncated, the log file will continue to grow.

Differences in Recovery Models

Under what circumstances will you encounter log space management issues? When the database is set to "Full" or "Bulk-Logged" mode, but transaction log backups are not performed.

• Full Recovery Model: All transactions are fully recorded. Supports Point-in-Time Recovery. Checkpoints do not automatically truncate; management via regular backups is required.
• Bulk-Logged Recovery Model: Uses minimal logging for bulk operations to improve performance. Checkpoints do not automatically truncate; management via regular backups is required. Fine-grained point-in-time recovery is not supported.
• Simple Recovery Model: Automatically truncates the log file at each checkpoint. Transaction log backups cannot be performed; you can only restore to the most recent backup point.

TIP

Regardless of the mode, the use of Begin Transaction is not affected.

Backup Strategy and Space Management

Under what circumstances do you need to adjust your backup strategy? When you find that backup files are too large, or database disk space is insufficient due to rapid log growth.

Backup Method Recommendations

• Full Backup: Backs up the entire database, including .mdf and .ldf files.
• Differential Backup: Backs up only the changes since the last full backup, saving space.
• Transaction Log Backup: Backs up all transactions since the last backup and performs truncation to release space.

Practical recommendation: Weekly full backups, daily differential backups, and transaction log backups every 15 minutes.

File Space Shrinking

If the database has been configured with excessive space, you can use DBCC SHRINKFILE to shrink it.

-- Shrink file size to 1000 MB
DBCC SHRINKFILE (LogicalFileName, 1000);

WARNING

The minimum limit for shrinking is the size of the used space. To effectively shrink the transaction log, it is recommended to perform a transaction log backup before executing DBCC SHRINKFILE, or temporarily change the recovery model to Simple to trigger truncation.

Analysis of Common Practical Issues

Under what circumstances will you encounter abnormal transaction log growth? When the database is set to "Full Recovery Model" but transaction log backups are ignored.

• Unlimited Transaction Log Growth: The log file will continue to expand until disk space is exhausted.
• Excessively Large Backup Files: Full backups will include all untruncated logs, resulting in massive backup file sizes.
• Ineffective Backup Strategy: If transaction log backups are not performed, the advantages of the Full model cannot be utilized; it is recommended to switch to the Simple model to improve performance.

Change Log

• 2024-10-17 Initial document creation.