Transaction Log recovery phases
Question
-
https://docs.microsoft.com/en-us/azure/azure-sql/accelerated-database-recovery
I am not quite sure the design for Phase 2 Redo , why it's not start scanning from the last commit lsn ?
All replies
-
Hi sakurai_db,
> I am not quite sure the design for Phase 2 Redo , why it's not start scanning from the last commit lsn ?
Good idea.
That’s why the Accelerated Database Recovery process was born. In the Standard database recovery process, Redo Phase from the minLSN, however, in Accelerated Database Recovery process, Redo from Transaction Log starts from last successful checkpoint (or oldest dirty page Log Sequence Number (LSN).
BR,
Mia
If the reply helped,please "Mark Answer" and upvote it.--Mia
""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.- Edited by MIAOYUXI Thursday, August 13, 2020 8:50 AM
- Proposed as answer by Tom Phillips Monday, August 24, 2020 1:41 PM
-
https://docs.microsoft.com/en-us/azure/azure-sql/accelerated-database-recovery
I am not quite sure the design for Phase 2 Redo , why it's not start scanning from the last commit lsn ?
Are you saying last checkpoint or last committed LSN. Last checkpoint is mentioned in picture, anyways if it starts from last committed LSN it will miss the transaction which started before the the commit corresponding to this LSN took place. Like in picture if after Oldest uncommitted transaction a transaction T1 starts and commit before database shuts down abruptly, and if sql server takes that committed LSN then it will miss the rollback of process which started before this transaction T1 leaving database in corrupt state as SQL Server will not know what to do with this transaction which was open. Hence Redo starts from oldest active transaction
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
My TechNet Wiki Articles
MVP- Proposed as answer by Tom Phillips Monday, August 24, 2020 1:41 PM
- Unproposed as answer by Tom Phillips Monday, August 24, 2020 1:41 PM
-
So if in between oldest uncommitted TX and last checkpoint, there are committed transaction, will they need to redo during phase 2 ?https://docs.microsoft.com/en-us/azure/azure-sql/accelerated-database-recovery
I am not quite sure the design for Phase 2 Redo , why it's not start scanning from the last commit lsn ?
Are you saying last checkpoint or last committed LSN. Last checkpoint is mentioned in picture, anyways if it starts from last committed LSN it will miss the transaction which started before the the commit corresponding to this LSN took place. Like in picture if after Oldest uncommitted transaction a transaction T1 starts and commit before database shuts down abruptly, and if sql server takes that committed LSN then it will miss the rollback of process which started before this transaction T1 leaving database in corrupt state as SQL Server will not know what to do with this transaction which was open. Hence Redo starts from oldest active transaction
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
My TechNet Wiki Articles
MVP
