When we work with SQL Server, we expect a committed transaction to remain safe even if the server suddenly crashes. One of the important mechanisms that makes this possible is Write-Ahead Logging (WAL).

The basic idea behind WAL is simple:

SQL Server writes the transaction information to the transaction log before writing the changed data page to disk.

This allows SQL Server to recover committed transactions after a failure.

A Simple Example

Consider a bank account:

CREATE TABLE Account
(
    AccountId INT,
    Balance DECIMAL(10,2)
);

INSERT INTO Account VALUES (101, 1000);

Now suppose we withdraw $100:

BEGIN TRANSACTION;

UPDATE Account
SET Balance = Balance - 100
WHERE AccountId = 101;

COMMIT;

After the update, the balance should be:

AccountId   Balance
---------   -------
101         900

But SQL Server does not necessarily write the changed data page immediately to the database file (.mdf).

Instead, the modified page is initially held in SQL Server's memory, called the Buffer Pool.

At the same time, SQL Server creates a record in the Transaction Log (.ldf) describing the change.

Conceptually:

UPDATE Account
       |
       v
Data Page in Memory
Balance = 900
       |
       +--------> Transaction Log
                   "1000 -> 900"

What Happens When COMMIT Runs?

When we execute:

COMMIT;

SQL Server needs to make sure the transaction is durable.

The relevant transaction log records are flushed to the transaction log file on disk.

Transaction Log
      |
      | Log Flush
      v
     .LDF

Once the log is safely persisted, SQL Server can confirm that the transaction has been committed.

The actual data page can be written to the .mdf file later.

This is the key idea behind Write-Ahead Logging:

Transaction Log → persisted first

Data Page → persisted later

What is an LSN?

SQL Server assigns LSNs (Log Sequence Numbers) to log records.

Think of an LSN as a position in the transaction log.

For example:

LSN 1001 → UPDATE Account

LSN 1002 → COMMIT

LSNs help SQL Server maintain the correct order of operations and perform recovery.

What Happens if SQL Server Crashes?

Imagine the transaction was committed, but the server crashed before the data page was written to the .mdf file.

The situation could look like this:

Transaction Log (.ldf)

UPDATE Account 1000 → 900

COMMIT

✓

Database Data File (.mdf)

Balance = 1000

← old value

When SQL Server restarts, it uses the transaction log to perform recovery.

Because it sees that the transaction was committed, SQL Server can redo the change:

1000 → 900

The database is therefore brought back to a consistent state.

What is a Checkpoint?

A checkpoint helps SQL Server write dirty pages from memory to disk and establish a recovery point.

So we can think of the process as:

UPDATE
  ↓
Data Page modified in memory
  ↓
Transaction Log written
  ↓
COMMIT
  ↓
Log Flush
  ↓
Transaction durable
  ↓
Checkpoint / background activity
  ↓
Data Page written to disk

Conclusion

Write-Ahead Logging is a fundamental part of SQL Server's reliability. It allows SQL Server to avoid writing every modified data page immediately while still protecting committed transactions.

The important concepts to remember are:

  1. LSN identifies the position of a change in the transaction log.
  2. Log Flush makes transaction-log information durable.
  3. COMMIT ensures the transaction's required log records are persisted.
  4. Checkpoint helps write dirty data pages to disk and supports recovery.
  5. WAL ensures the log is persisted before the corresponding data page is written.

Understanding these concepts also helps explain SQL Server performance topics such as WRITELOG waits, transaction-log growth, long-running transactions, and database recovery.

Happy Coding!