Microsoft® SQL Server™ 2000 Unleashed, Second Edition

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 the 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 containing log records for all completed transactions prior to the first log record of the oldest still-active transaction (see Figure 31.5). 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. This is because the log is pruned by ...