Database Reference
In-Depth Information
Chapter 29
Transaction Log Internals
As you already know, every database in SQL Server has one or more transaction log files in addition to data files.
Transaction logs store the information about all of the changes made in the database, and they allow SQL Server to
recover databases to transactionally consistent states in case of an unexpected shut down or crash.
In this chapter, we will examine the internal structure of the transaction log, discuss how SQL Server logs data
modifications, and review how it performs database crash recovery. We will also cover how to diagnose excessive
transaction log growth and discuss a few best practices related to log management and I/O file placement.
Data Modifications, Logging, and Recovery
SQL Server always keeps databases in a transactionally consistent state. Data modifications done from within
transactions must either be committed or rolled back in full. SQL Server never allows data to be transactionally
inconsistent by applying just a subset of the changes from uncommitted transactions.
This is true even when SQL Server shuts down unexpectedly. Every time SQL Server restarts, it runs a recovery
process on every database in the instance. SQL Server rolls back (
undo
) all changes from uncommitted transactions
and re-applies (
redo
) all changes done by committed transactions if they have not been saved into data files at the
time of the shutdown or crash.
The same process happens when you restore a database from the backup. There is no guarantee that all
transactions would have been completed at the time when the backup was run. Therefore, SQL Server needs to
recover the database as the final step of the restore process.
■
We will discuss the database backup and restore process in greater detail in Chapter 30, “Designing a
Backup Strategy.”
Note
The transaction log guarantees the transactional consistency of the data in the database. It consists of the stream
of the
log records
generated by data modification operations. Every log record has a unique, auto-incrementing
Log Sequence Number (LSN),
and it also describes the data change. It includes the information about the operation
and affected row; the old and new version of the data; the transaction that performed the modification; and so forth.
Moreover, some internal operations, such as CHECKPOINT, generate their own log records.
Every data page keeps the LSN of the last log record that modified it. At the recovery stage, SQL Server can
compare the LSNs of the log records from the log and data pages and find out if the most recent changes were saved to
the data files. There is enough information stored in a log record to undo or redo the operation if needed.
