How is an Active Log different from MinLSN?

Question

MinLSN is the LSN of the oldest uncommitted transaction:

1. What exactly does Active Log hold?
2. Isn't it supposed to hold all the uncommitted transactions?
3. Does it hold any other log records which are already committed?
4. What happens to MinLSN and Active Log when a CHECKPOINT happens?

Answer 1

MinLSN is the LSN of the oldest uncommitted transaction.

This is not exactly true

LSN stands for Log Sequence Number. Each log record has a LSN and each new log record is written to the logical end of the log with a higher LSN than the previous LSN.

Using this simplified Log Sequence:

LSN Log
80  Begin Tran 1
82  Begin Tran 2
83  Update Tran 2
84  Check Point
85  Update 1
86  Commit Tran 1
87  Check Point
88  Update Tran 2

Next LSNs will be 89, 90, ...

The Log Sequence Number of the first log record that is required to successfully perform a full recovery of the database is recorded in checkpoint records. This LSN is called Minimum recovery LSN or MinLSN.

MinLSN is the minimun of:

• LSN of the beginning of the oldest active transaction
• LSN of the start of the last checkpoint
• LSN of the beginning of the oldest replication transaction that has not yet been replicated

If we look at the above LSN records, MinLSN candidates are:

• Oldest active transaction = Begin Tran 2 with LSN 82
• LSN of the last checkpoint = LSN 87

Therefore MinLSN is LSN 82.

1. What exactly does Active Log hold?

The list of LSNs starting at MinLSN and up to the last recorded LSN is called the Active Log. Here Active log is composed of LSN 82 to 88. MinLSN is just the first LSN of the Active Log.

2. Isn't it suppose to hold all the uncommitted transactions?
3. Does it hold any other log records which are already committed?

You can see that the Active Log still contains already commited transactions (TRAN 1) because their LSNs are bigger than the MinLSN.

4. What happens to MinLSN and Active Log when a CHECKPOINT happens?

When a new checkpoint occurs and is recorded:

• LOG records linked to a Dirty page being written to disk are written from memory to disk before making any change to the page. This guarantees the successful recovery of the database before writing a page to disk. Since LOG are written sequentially, records in memory not related to the current page being written may have to be record before it.
• Dirty pages are written to disk (ie. all pages in memory that have been changed since the last checkpoint or since they were read from disk, however the status of the transactions),

• Checkpoint LOG record is created. Its LSN is compared to the LSN of the begining of the oldest active transaction:

  • If its LSN is smaller, it becomes the new MinLSN.
  • Otherwise, the LSN of the oldest trasaction remains the MinLSN.

Answer 2

To add a little more explanation to Julien's answer, if I may, a log record is needed for three reasons: rollback or replay (during recovery) and replication.

A record is not needed:

• for rollback if it is part of a transaction that is already committed.
• for replay if the pages it changed are already flushed to disk.
• for replication, if it is, obviously, already replicated.

So essentially a MinLSN is the LSN of the oldest record that is needed during recovery and replication.

Then, it makes sense to determine what records are no longer needed by getting the minumum of these:

• LSN of first record of oldest uncommitted transaction
• LSN of last checkpoint record
• LSN of last replicated record

Also, I find this article very explanatory of the related concepts: https://learn.microsoft.com/en-us/previous-versions/technet-magazine/dd392031(v=msdn.10)