locked
Transaction Log recovery phases RRS feed

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

    Thursday, August 13, 2020 3:15 AM

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
    Thursday, August 13, 2020 8:49 AM
  • 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
    Thursday, August 13, 2020 9:46 AM
  • Hi sakurai_db,

    Is the reply helpful?

    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.

    Friday, August 14, 2020 1:10 AM
  • Hi sakurai_db,

    Is the reply helpful?

    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.

    Monday, August 17, 2020 1:14 AM
  • Hi sakurai_db,

    Is the reply helpful?

    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.

    Tuesday, August 18, 2020 1:06 AM
  • 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

    So if in between oldest uncommitted TX and last checkpoint, there are committed transaction, will they need to redo during phase 2 ?
    Saturday, August 22, 2020 10:21 AM

  • So if in between oldest uncommitted TX and last checkpoint, there are committed transaction, will they need to redo during phase 2 ?
    Yes ofcourse, even this is in the definition of Redo phase in the Microsoft Docs link you shared

    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

    Saturday, August 22, 2020 2:01 PM