The SQL Server Transaction Log, Part 1: Logging Basics

All through my career as a data professional, both inside Microsoft and as a consultant, I’ve found one of the most misunderstood parts of a SQL Server database is the transaction log. Lack of knowledge of how the transaction log works and needs to be managed, or just simple misconceptions, can lead to all kinds of production problems, including:

• The transaction log growing out-of-control and potentially running out of space
• Performance issues from repeated shrinking of the transaction log
• Performance issues from a problem known as VLF fragmentation, which I discussed in this post
• The inability to recover to a desired point in time using transaction log backups
• The inability to perform a tail-log backup during disaster recovery (see here for an explanation of tail-log backups)
• Various issues around failovers and restore performance

With this post, I’m starting an occasional series on the transaction log and how it works and should be managed, and I’ll touch on all the problems above over its course. In this post, I’ll explain what logging is and why it’s required.

Basic Terminology Around Logging

When I’m talking about any mechanism in SQL Server, I find there’s a chicken-and-egg problem where I need to use a word or phrase before I’ve explained it. To avoid that problem in this series, I’m going to start by explaining some terminology that needs to be used when discussing logging, and I’ll expand on many of these terms as the series progresses.

Transaction, Commit, and Rollback

A transaction encompasses a change or a set of changes to a database. It has a defined beginning and a defined end. The beginning is when a BEGIN TRANSACTION statement is used, or SQL Server automatically begins a transaction for you. The end can be one of four things:

• The transaction commits when a COMMIT TRANSACTION statement is executed
• The transaction commits when SQL Server automatically commits the transaction in the case of an autocommit transaction
• The transaction finishes rolling back after a ROLLBACK TRANSACTION statement is executed
• The transaction finishes rolling back after a problem occurred, and SQL Server automatically rolled back the transaction

When a transaction commits, the changes the transaction made are finalized in the database and are durable in the SQL Server transaction log on disk. Note that I said, “in the transaction log.” The actual changes to the data file pages in memory are *not* written to disk when the transaction commits. They don’t need to be made durable in the data files because the changes are already durable in the transaction log. Eventually, the data file pages will be written to disk by a checkpoint operation.

Conversely, when a transaction rolls back, the data changes the transaction made are no longer present in the database. There will still be some physical changes in the database, as rolling back a transaction means performing more changes, but you can think of a rolled back transaction as not having affected the data in the database.

Checkpoints and rollback operations are topics worthy of their own posts, so I’ll explain them later in the series.

I discuss these three terms in much more depth in the tutorial Introduction to SQL Server Transactions on the SentryOne blog.

Logging, Log Records, and the SQL Server Transaction Log

Logging simply means creating a durable description of a change to a database, almost always in the context of a transaction. When a change is made, the change is described in a log record. A log record usually has enough information in it to allow the change to be replayed in the database or rolled back in the database if needed.

This log record will initially be in memory and may be written to disk before the transaction commits, but must definitely be written to disk before the transaction can finish committing, otherwise the transaction would not be durable. An exception to this rule is when the delayed durability feature is enabled, which Aaron Bertrand discusses in this post.

Log records are stored in the transaction log (one or more log files on disk), which has a somewhat complex internal architecture, and I’ll discuss that and much more on log records in the next post in the series.

Crash Recovery

A crash is where SQL Server shut down unexpectedly, and the various changed databases weren’t able to be shut down correctly (making sure all changed data file pages are written to disk and the database is marked as cleanly shut down).

When SQL Server starts up, it checks all the databases to see whether any were not marked as cleanly shut down. If it finds one, that database must go through crash recovery. This ensures the following:

• For any transaction that was committed before the crash, ensure all the changes in the transaction are reflected in the database (i.e., replay the transaction)
• For any transaction that was not committed before the crash, ensure none of the changes in the transaction are reflected in the database (i.e., roll the transaction back)

In other words, crash recovery makes a database transactionally consistent as of the time the crash occurred. Crash recovery is used:

• When SQL Server starts and finds a database that needs to be recovered
• During a failover to a secondary copy of a database
• At the end of a restore sequence involving backups (see here)

Crash recovery is a complex process and requires another few posts in the series before I can explain it in depth.

Why Is Logging Required?

The most basic reason for logging is to allow the SQL Server database to make transactions durable, so they can be recovered during crash recovery or rolled back if needed during normal database operations. If there was no logging, a database would be transactionally inconsistent and possibly structurally corrupt after a crash.

Without logging though, a host of other features in SQL Server would not be possible, including:

• Data backups that can be recovered consistently
• SQL Server transaction log backups that can be used during a restore operation and to implement log shipping
• Replication, which relies on being able to harvest transactions from the transaction log
• Change Data Capture, which uses the transactional replication Log Reader Agent to harvest changes from the transaction log
• Database mirroring and availability groups, which rely on sending log records to secondary copies of the database for replaying

SQL Server (and all similar database servers) uses what is called write-ahead logging. This means the descriptions of changes have to be written to disk before the changes themselves to guarantee the ability to properly crash recover a database. If a change was written to a data file before the log records describing it, and SQL Server crashed, there would be no way to know what to roll back, and the database would be inconsistent. This ordering is an invariant, no matter what isolation level, type of transaction, or whether the delayed durability feature is used. Log records first, data pages later.

Just the Tip of the Iceberg

As you can see from this introductory post, a huge amount goes into the transaction log and logging in a SQL Server database, and all I’ve done so far is define some high-level terminology and explain why logging is required. I hope you’ll join me as I branch out and go deeper as the series progresses!