SQL Server Transaction Log Internals -Logical Architecture

Understanding Logical Architecture, Log Records, LSNs, REDO and UNDO

Why Do We Need a Transaction Log?

Hi everyone!

Today, let’s understand the logical architecture of the SQL Server Transaction Log and why it plays such an important role in database operations.

Imagine a scenario where a customer updates a record in a database. The transaction completes successfully, but suddenly the SQL Server crashes before the updated data is written to the actual database data file.

After restarting the SQL Server, how does it know which transactions were completed? How does it identify which changes need to be applied and which incomplete transactions need to be rolled back?

This is where the Transaction Log comes into the picture.

The transaction log maintains a record of database modifications and helps SQL Server recover the database to a consistent state after an unexpected failure.

Before going deeper, let’s understand the two major perspectives of transaction log architecture.

Physical Architecture vs Logical Architecture

The SQL Server Transaction Log can be understood from two perspectives: physical architecture and logical architecture.

1. Physical Architecture

Physical architecture explains how SQL Server stores transaction log records inside the physical log file.

A transaction log file is divided into smaller sections called Virtual Log Files (VLFs). SQL Server uses these VLFs to manage the transaction log internally.

In simple terms, physical architecture explains how the transaction log is organized and stored on disk.

2. Logical Architecture

Logical architecture explains how SQL Server organizes and maintains transaction log records in a logical sequence.

It helps SQL Server identify:

• Which transaction generated a particular log record.
• In what order the modifications happened.
• How different log records are connected.
• Which changes must be reapplied during recovery (REDO).
• Which incomplete transactions must be rolled back (UNDO).

In this blog, we’ll focus on logical architecture and understand how SQL Server uses log records, Log Sequence Numbers (LSNs), transaction log chains, REDO and UNDO during recovery.

1. Understanding Transaction Log Records

Let’s take a simple example.

Assume a customer updates the Trade table:

UPDATE Trade
SET TradeStatus = 'Completed'
WHERE TradeID = 101;

When this transaction executes, SQL Server needs to maintain information about the modification.

It generates transaction log records describing the changes being performed.

These records contain information such as:

• The type of operation being performed.
• The transaction associated with the operation.
• The affected database page or allocation information, where applicable.
• The information required to redo or undo the operation.
• The relationship with previous log records.

The exact contents depend on the type of operation.

One important point to understand is that SQL Server does not simply write the entire updated database page into the transaction log for every modification. It generates appropriate log records that describe the changes required for recovery.

How does SQL Server write these records?

SQL Server uses a mechanism called Write-Ahead Logging (WAL).

The basic principle is simple:

The relevant transaction log records must be written to stable storage before the corresponding modified data page can be written to the data file.

Let’s understand the sequence:

1. A transaction starts and modifies a data page.
2. SQL Server generates the required log records.
3. These log records are placed in the log buffer in memory.
4. Before the transaction is considered committed, its required log records must be flushed to the transaction log file on disk.
5. The modified data page remains in the buffer pool as a dirty page.
6. Later, SQL Server writes the dirty page to the database data file.

This is the basic idea behind Write-Ahead Logging.

Notice that the transaction log is hardened before SQL Server confirms a successful commit. However, the modified data page does not need to be written to the MDF file immediately.

This is what makes transaction log recovery possible.

2. Understanding Log Sequence Numbers (LSNs)

Now that we understand how SQL Server generates log records, let’s look at how it identifies and maintains their sequence.

SQL Server uses something called a Log Sequence Number (LSN).

An LSN is a unique identifier assigned to a log record. It helps SQL Server identify the logical position of a log record within the transaction log.

Think of it as a sequence number that helps SQL Server understand the order in which log records were generated.

For example:

LSN 1001  → Transaction started
LSN 1002  → Trade record modified
LSN 1003  → Another modification
LSN 1004  → Transaction committed

These are illustrative LSNs, not actual SQL Server LSN values.

LSNs are important because SQL Server uses them to:

• Maintain the logical sequence of log records.
• Identify the progress of transactions.
• Determine which log records are required during recovery.
• Support backup and restore operations.
• Track database changes and recovery progress.

How is an LSN represented?

An LSN is commonly represented in three parts:

VLF Sequence Number : Log Block Offset : Log Record Offset

For example:

00000025:000001A0:0003

This is an illustrative representation.

The important thing is that an LSN identifies the logical position of a log record, rather than simply identifying a physical file location.

3. Understanding Transaction Log Chains

Now let’s understand how SQL Server connects log records belonging to the same transaction.

A transaction may generate multiple log records while executing.

For example:

BEGIN TRANSACTION;

UPDATE Trade
SET TradeStatus = 'Completed'
WHERE TradeID = 101;
UPDATE Trade
SET TradeAmount = 5000
WHERE TradeID = 101;
COMMIT TRANSACTION;

Here, multiple operations happen within a single transaction.

SQL Server maintains the relationship between these log records using transaction-related information, including the previous LSN (PrevLSN).

The PrevLSN field points to the previous log record belonging to the same transaction.

For example:

Transaction T1

LSN 1001 → BEGIN TRANSACTION
              |
              ↓
LSN 1002 → First UPDATE
              |
              ↓
LSN 1003 → Second UPDATE
              |
              ↓
LSN 1004 → COMMIT

Conceptually, the transaction’s log records are connected through their PrevLSN references.

This relationship is called a transaction log chain.

Why is this important?

Imagine that the transaction performs three updates but fails before committing.

Get Nathan2kgem’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SQL Server needs to identify all the changes made by that incomplete transaction and reverse them.

By following the transaction’s log chain, SQL Server can locate the relevant log records and perform the required UNDO operations.

So, the transaction log chain helps SQL Server maintain transaction history and recover incomplete transactions.

4. Understanding REDO — Reapplying Changes

Now let’s move into one of the most important concepts in transaction log recovery: REDO.

Consider the following scenario.

A customer updates the Trade table and commits the transaction successfully.

The transaction log records are safely written to disk, but the modified data page is still present only in the buffer pool.

Suddenly, the SQL Server crashes.

When SQL Server restarts, the data page changes that were present only in memory are lost.

But the transaction log records are still available on disk.

How does SQL Server recover the committed transaction?

This is where REDO comes into the picture.

REDO means reapplying the changes recorded in the transaction log to ensure that committed transactions are reflected in the database data files.

For example:

Before the crash:

TradeID: 101
TradeStatus: Pending

Transaction executed:

UPDATE Trade
SET TradeStatus = 'Completed'
WHERE TradeID = 101;

The transaction committed successfully, but the updated data page was not written to the MDF file before the crash.

During recovery, SQL Server identifies the committed transaction and replays the necessary log records.

After REDO:

TradeID: 101
TradeStatus: Completed

The committed change is recovered even though the original data page modification was lost from memory.

This is called REDO.

5. Understanding UNDO — Rolling Back Incomplete Transactions

Now let’s consider another scenario.

A transaction starts and performs multiple updates, but SQL Server crashes before the transaction commits.

For example:

BEGIN TRANSACTION;

UPDATE Trade
SET TradeStatus = 'Completed'
WHERE TradeID = 101;
UPDATE Trade
SET TradeAmount = 5000
WHERE TradeID = 102;
-- SQL Server crashes before COMMIT

Here, the transaction has performed modifications, but it has not successfully committed.

Should SQL Server retain these changes after restarting?

No. An incomplete transaction must be rolled back to maintain database consistency.

This is where UNDO comes into the picture.

UNDO means reversing the changes made by incomplete or uncommitted transactions.

SQL Server uses the transaction log records to identify the changes that need to be reversed.

For example:

Before the transaction:

TradeID 101 → Pending
TradeID 102 → Amount 3000

The transaction modifies both records but crashes before committing.

During recovery, SQL Server identifies the incomplete transaction and performs UNDO.

After UNDO:

TradeID 101 → Pending
TradeID 102 → Amount 3000

The database returns to the state before the incomplete transaction.

An important point: SQL Server does not simply delete the previous log records during UNDO. It performs the necessary rollback operations and generates compensation log records (CLRs) to record the undo work.

These compensation log records are also important if another failure occurs during recovery.

6. How Does SQL Server Perform Recovery?

When SQL Server restarts after an unexpected shutdown, it performs database recovery to bring the database into a consistent state.

The recovery process is generally explained through three phases:

1. Analysis Phase

SQL Server examines the transaction log to understand the state of the database at the time of the crash.

It identifies:

• Transactions that were active.
• Transactions that were committed.
• Transactions that were incomplete.
• The dirty pages that may require recovery.

In simple terms, the Analysis phase determines what happened before the crash.

2. REDO Phase

SQL Server replays the required log records to ensure that the database reflects all necessary changes.

This includes committed changes that might not have reached the data files before the crash.

SQL Server uses recovery information, including page LSNs, to determine whether a particular change needs to be reapplied.

3. UNDO Phase

SQL Server identifies transactions that were not committed before the crash.

It reverses their incomplete changes using the transaction log and records the rollback operations.

After these phases, the database is brought back to a transactionally consistent state.

The overall recovery flow looks like this:

SQL Server Restart
                 |
                 ↓
          Analysis Phase
                 |
                 ↓
       Identify Transactions
       and Dirty Pages
                 |
                 ↓
            REDO Phase
                 |
                 ↓
       Reapply Required Changes
                 |
                 ↓
            UNDO Phase
                 |
                 ↓
       Roll Back Incomplete
           Transactions
                 |
                 ↓
       Database Recovery Complete

7. What Happens to Transaction Log Records After a Checkpoint?

One common misunderstanding is that SQL Server deletes transaction log records immediately after a checkpoint.

Let’s clarify this.

A checkpoint is a process that writes dirty data pages from the buffer pool to the database data files and records checkpoint-related information in the transaction log.

It helps reduce the amount of work required during database recovery.

However, a checkpoint does not mean that all transaction log records can immediately be removed.

SQL Server can reuse log space only when the relevant log records are no longer required for recovery or other operations.

For example, active transactions, log backups in the FULL recovery model, replication, availability features and other log consumers can prevent log truncation.

When log truncation happens, SQL Server marks eligible portions of the log as reusable.

The physical VLFs are not deleted or automatically removed from the transaction log file. Their space can be reused by future transactions.

This is why transaction log truncation and transaction log file shrinking are two different operations.

Final Thoughts

The SQL Server Transaction Log is much more than a file that stores database changes. It is one of the most important components responsible for maintaining database consistency and supporting recovery.

To summarize:

• Log Records: Maintain information about database modifications.
• LSN: Identifies the logical position of each log record.
• Transaction Log Chain: Connects log records belonging to the same transaction.
• REDO: Reapplies the required changes during recovery.
• UNDO: Reverses incomplete transactions.
• Checkpoint: Helps reduce recovery work by writing dirty pages to disk.
• Log Truncation: Makes eligible log space available for reuse.

Understanding these concepts gives us a better picture of what SQL Server does internally when a transaction executes, commits, rolls back or when the database recovers after an unexpected failure.

In the next part, we’ll explore the physical architecture of the transaction log, including VLFs, log blocks, log buffers and how SQL Server manages transaction log space.