How the SQL Server Transaction Log Works for You

The transaction log lies at the core of SQL Server, but it’s often misunderstood. Understanding the log architecture will give you peace of mind and make you a better administrator.

The transaction log is a double entry system. Every activity is recorded in the log before it is carried out in the database. So what’s the purpose of all this extra work?

a)    To provide atomicity – a database transaction is ‘all or nothing’. If a transaction does not finish, the data must be restored to its original state. The transaction log provides this rollback facility.

b)   To provide durability – a completed transaction must never be lost. The transaction log allows the exact state of the system to be recovered in the event of a crash.

Atomicity and Durability are the ‘A’ and ‘D’ of the ACID database properties (Atomicity, Consistency, Isolation, Durability).

Understanding the SQL transaction log architecture will give you peace of mind and make you a better administrator.

Let’s look in more detail at how the log works.

Transaction Log Implementation

Every operation that is performed on a SQL database is appended to the end of a log. Each log entry has a ‘log serial number’ (LSN) and records a before and after snapshot of all data that is being changed.

In default configuration, SQL does not allow these log records to be cleared away until a backup procedure has been performed.

Every 8KB page of data in the database stores the last LSN that affected it, which allows SQL to verify that the database is in sync with the log. The LSN on each page is updated when SQL writes data from the buffer cache (RAM) to the physical data file – this is known as a ‘checkpoint’. The start and end of the checkpoint process is also recorded in the log.

Let’s look at how the transaction log provides atomicity and durability.

Rollback (Atomicity)

If a transaction cannot be completed because a business rule was broken, or the action was cancelled, or the user’s laptop ran out of battery, SQL must ‘rollback’ whatever changes have been made so far, to leave the database in its original condition.

SQL performs this rollback by running through the transaction log in reverse and restoring each piece of data to its ‘before’ state.

Recovery (Durability)

Resuming from an Outage

If your SQL server loses power, the database is left in a murky state:

• Changes from recently finished transactions may have been in the buffer cache and not yet written to the data file.
• Transactions that were in progress when the server lost power must be undone, because SQL does not know how they should be completed.
• Long-running transactions may have been partially written out to the data file.

Luckily, the transaction log can be used to get out of this mess!

The algorithm is to Redo, and then Undo:

• Redo, in order, the operations from the transaction log. Many operations can be skipped as they were already saved to the data file (which we can determine from the checkpoint log entries, and from each page’s LSN). After Redo, the database is in the exact state that it was when the power went out.
• Undo, every transaction that was unfinished at the time of the failure needs to be rolled back. This is the same process as a regular rollback (see above).

If you have ever seen a database labelled as “Recovery” in SQL Management Studio, SQL is in the middle of performing this process.

After Redo then Undo, the database is now in a consistent and safe state: every transaction has either been fully saved or fully undone.

Complete Database Recovery

If a disaster leads to the loss of data files, a database can be recovered by restoring the last full database backup then replaying the transaction log to redo everything that happened since the full backup.

If your backups and the active transaction log has survived the disaster, then not a single finished transaction will be lost. If only your backups survive, you will lose some data depending, on the frequency of your log backups.

Conclusion

The transaction log is an impressive piece of engineering. It provides critical guarantees with little overhead, in a highly concurrent system. The transaction log also supports other database services such as replication and change data capture.

In my next blog I will look at the practical aspects of SQL Server durability: managing backups and recovery.

Further reading:

• SQL Server Transaction Log Architecture and Management Guide on MSDN
• My previous post: How SQL Server Manages Memory