October 5, 2026 13:08 by
PeterWhen the transaction is abruptly abruptly abruptly abruptly abruptly abruptly abruptly abruptly ends. Write-Ahead Logging (WAL) is one of the key methods that enable this.
The core principle underlying WAL is simple:
Before writing the modified data page to disk, SQL Server records the transaction details in the transaction log.
This makes it possible for a failure.
A Basic Illustration
Think about 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:
- LSN identifies the position of a change in the transaction log.
- Log Flush makes transaction-log information durable.
- COMMIT ensures the transaction's required log records are persisted.
- Checkpoint helps write dirty data pages to disk and supports recovery.
- 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!
HostForLIFE.eu SQL Server 2025 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
