Table of Contents
Introduction ___________________________________________ 11
Chapter 1: Meet the Transaction Log ______________________ 15
How SQL Server Uses the Transaction Log ________________________________ 15
Write Ahead Logging and Transactional Consistency _______________________16
Transaction Log Backup and Restore _____________________________________18
Controlling the Size of the Log _________________________________________ 20
A Brief Example of Backing up the Transaction Log ________________________22
Summary ___________________________________________________________ 26
Chapter 2: Some, But Not Too Much, Log Internals __________27
Virtual Log Files and the Active Log ______________________________________27
Log Truncation and Space Reuse ________________________________________ 31
A Peek at How SQL Server Grows the Log _________________________________ 33
Summary ___________________________________________________________ 42
Chapter 3: Transaction Logs, Backup and Recovery __________43
The Importance of Backups _____________________________________________43
What Backups Do I Need to Take? _______________________________________45
Database Recovery Models ______________________________________________47
Choosing the right recovery model ____________________________________48
Setting the recovery model __________________________________________ 49
Discovering the recovery model _______________________________________50
Switching models ___________________________________________________ 53
Log Backup Logistics __________________________________________________54
Frequency of log backups ____________________________________________54
Preserving the log chain _____________________________________________ 55
Storing log backups _________________________________________________56
Automating and verifying backups _____________________________________ 57
Summary ____________________________________________________________58
