Microsoft® SQL Server 2008 R2 Unleashed

Long-Running Transactions

As you have already seen, transaction information is recorded in each database’s transaction log. However, long-running transactions can be a cause of consternation to a system administrator who is attempting to back up and prune the transaction log. Only the inactive portion of the log can be truncated during this operation. The inactive portion of the log is the pages that contain log records for all completed transactions prior to the first log record of the oldest still-active transaction (see Figure 31.8). Even if completed transactions follow the first record of the oldest active transaction, they cannot be removed from the log until the oldest active transaction completes. The reason is that the log is pruned ...