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 committed but not written to the data file.
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 file in virtual blocks without a specific 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 file
- Inactive transactions: All those transactions before the earliest active transaction
Because transactions are of varying duration, and are ...
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.