563
Chapter 21: Backup and Recovery Planning
21
Working with the Transaction Log
Sometimes it seems that the transaction log has a life of its own. The space within the
file seems to grow and shrink without rhyme or reason. If you’ve felt this way, you’re
not alone. This section should shed some light on why the transaction log behaves as
it does.Inside the Transaction Log
The transaction log contains all the transactions for a database. If the server crashes the
transaction log, both transactions that have been written are used for recovery by rolling
back uncommitted partial transactions and by completing any transactions that were com-
mitted but not written to the data fi le.Virtually, the log can be imagined as a sequential list of transactions sorted by date and
time. Physically, however, SQL Server writes to different parts of the physical log fi le in
virtual blocks without a specifi c order. Some parts might be in use, making other parts
available, so the log reuses itself in a loose round-robin fashion.The Active and Inactive Divide
The transactions in the transaction log can be divided into two groups (see Figure 21-5):■ (^) Active transactions: Uncommitted and not yet written to the data fi le
■ (^) Inactive transactions: All those transactions before the earliest active
transaction
FIGURE 21-5
The inactive transactions are all those prior to the oldest active transaction.
Virtual Log file 1
Unused Inactive Unused
Committed
transactions Checkpoints
Oldest uncommitted
transaction (MinLSN)
Uncommitted
transaction
Active
Virtual Log file 2 Virtual Log file 3 Virtual Log file 4
Because transactions are of varying duration, and are committed at different times, it’s
likely that committed transactions are in the active portion of the log. The active portion
does not merely contain all uncommitted transactions, but all transactions since the start
of the oldest uncommitted transaction. One old uncommitted transaction can make the
active portion appear unusually large.
c21.indd 563c21.indd 563 7/31/2012 9:22:50 AM7/31/2012 9:22:50 AM
http://www.it-ebooks.info