4

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?
2

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.

  1. Isn't it suppose to hold all the uncommitted transactions?
  2. 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.

  1. 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.
  • CHECKPOINT and its effect on the recovery process. I understand that it is writing the dirty pages to the disk, but what is the point? It will speed up the recovery process and per BOL but if the CHECKPOINT happened in the middle of an uncommitted transaction, we again have to roll back the changes, right ? I cannot see the difference with and without CHECKPOINT in the recovery process. And there are so many places providing different blocks of information on different things but I am unable to find a one article covering all these concepts together. :( – karun_r Jan 28 '16 at 17:36
  • What has already been written will only have to be rollback if there is a rollback. If data is saved (fully or partially) and the server crash, once back online it can start over from the last checkpoint and replay the log from minlsn to the last record because it has been recorded. Already written pages won't be written again and won't be rollback. It supposed that there is no corruption or something wrong or missing whatsoever. – Julien Vavasseur Jan 28 '16 at 17:49
  • Beside what is already saved won't have to be done later when more data must be saved adding more pressure and memory can be released if it is needed somewhere else. It is kind of expecting that a commit is more likely than a rollback. – Julien Vavasseur Jan 28 '16 at 17:51
0

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://docs.microsoft.com/en-us/previous-versions/technet-magazine/dd392031(v=msdn.10)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.