A Brief Introduction to SQL Server Transaction Logs

Types of Database Files

In SQL Server, creating a database typically generates two primary files:

• Master Database File:

  The file extension is ".mdf". This is the core file of the SQL database, containing the database's structural information (such as tables, indexes, views, and stored procedures) as well as all user and system data.

• Log Database File:

  The file extension is ".ldf", used to record transaction logs for all data changes. Even if the database fails, these logs can be used for data recovery, ensuring data consistency and integrity.

When performing database operations, data changes are first recorded in the transaction log (.ldf). When a Checkpoint is triggered, SQL Server writes all committed transaction changes from the transaction log (.ldf) to the master database file (.mdf).

Recovery Models

SQL Server provides three recovery models:

• Full Recovery Model:

  • All transactions are fully recorded in the transaction log, including all operations such as inserts, updates, and deletes.
  • Transaction log backups can be performed, and these backups can be used for Point-in-Time Recovery to restore the database to a specific point in time.
  • When a checkpoint operation is executed in this mode, the transaction log is not automatically truncated; it must be managed through regular backups.

• Bulk-Logged Recovery Model:

  • Uses minimal logging for bulk operations (such as large-scale inserts, updates, deletes, or index rebuilds) to reduce log file size and improve performance.
  • Individual operations are still recorded normally.
  • Checkpoint operations do not automatically truncate the transaction log. Regular transaction log backups are required to manage file size.
  • Can only be restored to the point in time before or after a bulk operation; fine-grained point-in-time recovery is not possible.

• Simple Recovery Model:

  • The transaction log is automatically truncated at each checkpoint. This reduces the size of the log file, but it also means that transaction log backups cannot be performed, and recovery is limited to the last backup or the most recent state of the database.

Transaction log truncation is the process of marking committed transaction records in the log as reusable, allowing SQL Server to reuse this space and prevent the transaction log from growing indefinitely.

TIP

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

Backup Modes

SQL Server provides three main backup methods:

• Full Backup:
  • A full backup backs up the entire contents of the database, including the master database file (.mdf) and the transaction log (.ldf).
  • A full backup can be used to restore the database to its complete state at the time of the backup.
• Differential Backup:
  • A differential backup only backs up data that has changed since the last full backup, saving time and space.
  • During restoration, you must first restore the latest full backup and then restore all relevant differential backups to recover to a specific point in time.
• Transaction Log Backup:
  • A transaction log backup backs up all transaction logs since the last transaction log backup.
  • Performing a transaction log backup truncates the committed transaction records in the log, allowing SQL Server to reuse the space and prevent the transaction log from growing indefinitely.
  • A full backup must be performed before the first transaction log backup.

In practice, a common backup strategy is to perform a full backup once a week, a differential backup once a day, and a transaction log backup every 15 minutes. The specific frequency can be adjusted according to actual needs.

Database File Space Management

Database File Growth Strategy

When creating a database, you can set the following file growth strategies:

• Automatic Growth:
  • File growth method:
    • By percentage: The growth size is a percentage of the current file size.
    • By MB: The growth size is a fixed number of MB.
  • Maximum file size:
    • Limited to __ (MB): Sets the maximum size of the file; it will no longer grow automatically once this limit is reached.
    • Unlimited: The file can expand indefinitely until disk space is exhausted.
• Do not grow automatically: If set to not grow automatically, the file size will not increase automatically and must be adjusted manually.

Types of Database Space

• Allocated Space: Refers to the total space allocated in the database files (such as .mdf and .ldf), including both used and unused portions. This is the total size of the file, i.e., the actual size of the database file on the disk.
• Used Space: Refers to the space actually used to store data, including space occupied by tables, indexes, views, etc. This does not include empty or unused portions.

Impact of Growth Strategy

When the used space of the database exceeds the allocated space, the system will automatically increase the allocated space based on the growth strategy. If the maximum file size limit is reached and there is no remaining unused space, the database will no longer be able to write data.

Shrinking Allocated Space

If you need to shrink the allocated space of a database file, you can use the following command to reduce the file size. The logical name can be found in the "Files" page of the database properties in SQL Server Management Studio (SSMS).

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

Notes:

• 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 change the recovery model to Simple to trigger transaction log truncation.
• It is not recommended to shrink the master data file, as this may cause the transaction log to grow rapidly.

Common Practical Issues

In practice, a common issue is that a database is set to the Full recovery model but transaction log backups are not performed. This leads to several major problems:

• Unlimited Transaction Log Growth:

  If transaction log backups are not performed, the transaction log will continue to grow until it reaches the set space limit, consuming a large amount of disk space.

• Oversized Backup Files:

  A full backup will include all untruncated transaction logs, resulting in massive backup files and increasing the time and space required for backups. If transaction log backups are performed, the full backup will not include these already-backed-up transaction logs, making the backup file relatively smaller.

• Ineffective Backup Strategy:

  The advantage of the Full and Bulk-Logged models lies in the ability to perform transaction log backups. If transaction log backups are not used, it is better to use the Simple model, which may offer better performance.

Change Log

• 2024-10-17 Initial document creation.