Microsoft SQL Server 2012 Bible

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

Figure 21.5 The inactive transactions are all those prior to the oldest active transaction.

Because transactions are of varying duration, and are ...