Anatomy of a SQL Server Transaction Log
Recently, we discussed the role of the recovery model in establishing how SQL Server manages database transaction logs. But what is the SQL Server log composed of? How does the logging process work? In this post, we will dissect the SQL Server transaction log to uncover its core anatomy.
First, what is the transaction log?
The SQL Server transaction log is a crucial element within each database that records all database changes across time. This log enables point-in-time recoverability and rollbacks in the event of mistakes, corruption, or failure. This recoverability is critical to ensuring the integrity and resilience of the database.
What is contained in the transaction log?
There are several key concepts and log components to be aware of when dealing with SQL Server’s transaction logs.
1. Log Records
Within SQL Server’s transaction logs, individual change operations are enclosed within log records. Each log record represents one UPDATE, INSERT, or DELETE operation in the database and is associated with one transaction. Each transaction, however, may have numerous log records.
Every data modification made in the database has one or more log records associated with it. Log records contain either the logical operation performed with the modification made, or they contain before and after images of the modified data. Start and end times of each transaction are also recorded.
2. Log Blocks
A log block is the basic unit of I/O when it comes to transaction logs. The sizes of the log blocks vary but are at least 512 bytes and can be as large as 60 KB. Log blocks can contain multiple log records or none. According to Microsoft, “a log block is a container of log records that’s used as the basic unit of transaction logging when writing log records to disk.”
3. Virtual Log Files (VLFs)
When considering the database transaction log, it is important to keep in mind the concept of the physical log file and its composite VLFs, or virtual log files.
The division of the log into multiple log segments (or virtual logs) is handled automatically by SQL Server and is the foundation of the circular nature of the physical log file (see below). Each VLF is in turn composed of numerous log blocks.
VLFs are created dynamically as needed by SQL Server. While we as administrators are not able to set a static size for these file divisions, we can manage the auto-growth settings of the log file, which will in turn impact the size and number of the VLFs that SQL Server creates.
4. Log Sequence Number (LSN)
Within the database’s transaction log, the chronological consistency of transactions is preserved across the VLFs through LSNs, or log sequence numbers. These numbers allow SQL Server to arrange the transactions in their correct chronological order for the purposes of rollback and recovery despite the potential for having been written to different VLFs.
The LSN is a concatenation of the VLF ID, the Log Block ID, and the Log Record ID separated by colons. You can see examples of the LSN by querying the sys.dm_db_log_info view and looking at the [vlf_create_lsn] field:
5. Minimum Recovery LSN (MinLSN)
The MinLSN, or minimum recovery LSN,is the LSN that would be needed for a full recovery of the database. Put another way, the MinLSN is the first LSN in the sequence of the active section or tail of the log, which is the section of the log that has not yet been written from memory to disk by a checkpoint operation.
How does SQL Server logging work?
6. Checkpoint Process
Checkpoints are the SQL Server operations by which database modifications that are being stored in memory are written to disk. Checkpoints are usually triggered by a log backup or automatically by the recovery interval setting of the database.
The active section of the log that has not yet reached a checkpoint can never be truncated, so paying attention to the recovery settings of each database helps to manage checkpoints and transaction log truncation optimally.
7. Cyclical Logging
The SQL Server transaction log can be thought of as a revolving log file.
Consider that the physical log is allocated a designated amount of space in the database properties when it is configured. As mentioned above, the transaction log is made up of smaller segments (VLFs) that SQL Server writes to as part of its internal logging process – adding a new VLF each time the last one fills up. This process is internally controlled by the application.
If the physical transaction log is never truncated, it will continue to grow, adding new VLFs as it grows, until it uses up all the space available to it and SQL Server fails.
If, however, the transaction log is truncated, older VLFs containing transactions with LSNs prior to the MinLSN will be cleared. When those files are truncated, the space that was being used by the deleted log records will be released and made available for new log records to be created as more transactions are committed. This circular process allows the overall size of the log to remain consistent despite ongoing transaction logging.
This log-and-truncate cycle will continue successfully unless the logging rate exceeds the truncation rate. Note once again, however, that truncation of the transaction log cannot occur in full or bulk-logged recovery models if log backups are not taken first.
For this reason, administrators should pay careful attention to the recovery and log file settings to avoid unnecessary disruptions in the SQL Server environment.
What else should you know about SQL Server transaction logs?
In an upcoming post, we will discuss how to troubleshoot some common issues with SQL Server transaction logs and give some best practices for transaction log management.
When configured carefully, the logging process within SQL Server can operate seamlessly for years without DBA intervention.
Here are some resources that contain more information about some of the topics we touched on:
- Log Blocks
- LSNs
- Virtual Log Files and Growth Algorithms
- SQL Server Checkpoint Process
- Transaction Log Backups and Log Shrinking
Want to work with The SERO Group?
If you’re concerned about your backup strategy, or more to the point, your ability to restore a critical database, contact us. We’re happy to help.