while reading an article from BOL, I stuck on the picture which explains the recovery process of database (without ADR):

enter image description here

Shouldn't Phase 2:Redo start from Last CHECKPOINT as till that point everything is already in data file? Why does it starts from minLSN?


The Database Checkpoints doc says that

For performance reasons, the Database Engine performs modifications to database pages in memory-in the buffer cache-and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also records the information in the transaction log.

So the CHECKPOINT is not directly related to transactions, instead it's related to dirty pages which might not belong to the same transaction and that explains why the recovery process of a database must consider the MinLSN instead of the last CHECKPOINT as you suggested.

If you take a look at the structure of the Transaction Log Physical Architecture, you'll see that a checkpoint can happen in a VLF that is in front of the oldest active VLF as the following picture ilustrates:

VLFs in a log file

With that in mind, think that a really long transaction (T1) started using Virtual Log 3 and only completed by the end of Virtual Log 4. During the execution of T1 another transaction happened (T2), but this one started and completed after the CHECKPOINT. Now imagine a crash happened. If the recovery process would consider as starting point the CHECKPOINT, it would be able to redo T2, but not to grant a consistent state to the recovered database for the crash of the database happened after the CHECKPOINT but before T1 had completed, leaving half a transaction registered when it should be rolled back.

You can also see that example on the picture in your question where the Oldest uncommitted Tx started before the Last Checkpoint.

  • let's say we issue a statement: begin tran delete from users where id=1 before checkpoint and we do not commit it yet. Afterwards, assume that checkpoint happens which means that user with id = 1 is deleted from users table(reflected in data file). The question is : is it important to redo uncommitted transaction meaning begin tran delete from users where id=1 if I already do not have that user in the table because after checkpoint that user is deleted from table? – RaufDBA Apr 8 '20 at 14:48
  • You must observe that the three phases of recovery (Analysis, Redo and Undo) compose a procedure intended to solve both uncommitted transactions that should be undone and committed transactions that should be registered. That delete you issued wasn't committed in your example, so the phase that matters the most for that transaction is not the Redo, but the Undo. Check this Overview. When it says the data is written from memory to disk it means the log file. – Ronaldo Apr 8 '20 at 15:24
  • @RaufDBA, notice that to grant consistency data can't be written on the tables in datafile (.mdf or .ndf) before it's been written to the log file. If it was possible, and the database crashed before you committed that transaction, the recovery would bring online a database with deleted users of id = 1 from a transaction that didn't complete and there was no way to be rolled back, for the crash occurred after the user was removed from the table on disk but before the transaction was registered on the log (so, for the matter of rollback, it never existed). – Ronaldo Apr 8 '20 at 15:25

Actually, if a transaction was still open while the checkpoint was issued, this transaction need to be either replayed or rolledback. (And would be miss if it started at the checkpoint)

That's why it uses the minLSN

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.