locked
Why Does a StandBy Database Need an Undo file? RRS feed

  • Question

  • Does a Standby database have an .ldf file?

    It was my understanding that a database only needs an .mdf file and an .ldf file.

    If these two files are insufficient - if an undo file is also needed - wouldn't all SS databases need that third file?

    To a small extent, I do understand that the undo file is used for undoing and redoing uncommitted transactions, but I don't understand why all that can't be handled by a standard .ldf file.


    • Edited by jal2 Sunday, November 30, 2014 12:56 AM
    Sunday, November 30, 2014 12:56 AM

Answers

  • "Again, because it is needed for recovery to be run, the database to be consistent, the database to allow connections, users allowed to read data (read only), and still allow for future log restores. The links I posted tell you exactly what the undo file holds and how it is used."

    But all these 'needs' also apply to non-standby recoveries so, again, you're not explaining why the undo file is needed ONLY for standby databases.

    The difference seems to be this. (As you say, let's not mince words, so I will use the word 'recovery' here in reference to the commonalities of standby and recovery). With ordinary recovery, the rollback of dirty records only occurs once (at the very end of the process) - with no possibility of a redo. Those proposed changes are gone, forever.

    Whereas with standby-recovery (as I've consented to the word 'recovery' here to avoid mincing words), the rollback of dirty records occurs ON EVERY RESTORE (that's the first difference) and with provision made for a possible redo so the changes aren't gone forever (that's the second difference). Since the proposed changes are still relevant (in a sense 'still pending' to speak very loosely/imprecisely), the  undo file is needed to record them so they are not forever lost.

    As I see nothing wrong with this analysis, I will soon mark this post as answer unless someone else quickly jumps in with a more persuasive one.

    • Marked as answer by jal2 Sunday, November 30, 2014 2:38 PM
    Sunday, November 30, 2014 6:02 AM

All replies

  • Hello,

    When restoring databases in SQL Server, any restore can have an undo file associated with it - in fact it can be handy when walking through log restores to find a point in time that something was changed.

    Specifically to standby logshipping databases, the file is used so that further restores can continue to happen and not break lsn chain continuity - which would stop all further restores. For databases in SQL Server to be accessible, they must be in a known state, this requires recovery to be run. When recovery is run, some transaction may be undone - and this is what would break the restore ability of the standby database if the file was not used.

    -Sean 


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Sunday, November 30, 2014 1:34 AM
    Answerer
  • Hello,

    When restoring databases in SQL Server, any restore can have an undo file associated with it - in fact it can be handy when walking through log restores to find a point in time that something was changed.

    Specifically to standby logshipping databases, the file is used so that further restores can continue to happen and not break lsn chain continuity - which would stop all further restores. For databases in SQL Server to be accessible, they must be in a known state, this requires recovery to be run. When recovery is run, some transaction may be undone - and this is what would break the restore ability of the standby database if the file was not used.

    -Sean 


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Thanks! I'm sure you're correct but I don't think this really clarifying the answer. A regular .ldf file is supposed to be capable of undoing/redoing transactions as needed, so I'm not seeing how you've CLEARLY explained why an .ldf file can't suffice in all cases (I'm probably just not understanding).

    However, I have formed a possible answer to my own question, as follows. In a normal situation, a data file is permitted to have dirty records and an .ldf file is designed accordingly. But it seems MS decided that a Standby database shall NOT have any dirty records and hence the usual architecture (a standard .ldf file) won't suffice. Thus the undo file provides the extra scaffolding needed to handle this unusual arrangement. Do you suppose I'm correct? Or hopelessly confused, more likely?


    • Edited by jal2 Sunday, November 30, 2014 1:47 AM
    Sunday, November 30, 2014 1:46 AM
  • Hello,

    A regular .ldf file is supposed to be capable of undoing/redoing transactions as needed, so I'm not seeing how you've CLEARLY explained why an .ldf file can't suffice in all cases

    When recovery is run to bring the database online, redo is going to occur and put it in the state that it was at the time of the backup. Once redo is finished, undo is going to occur and make changes to the database and the log in order to get to a consistent state. Because, now, the log file has been written with anti-operations for the undo portion, no other restores can happen as the LSNs will be out of sync. Thus your "standby" database is no longer standby. Thus the extra file holding undone transactions that will be needed should further log restores occur.

    However, I have formed a possible answer to my own question, as follows. In a normal situation, a data file is permitted to have dirty records an .ldf file is designed accordingly. But it seems MS decided that a Standby database shall NOT have any dirty records and hence the usual architecture (a standard .ldf file) won't suffice. Thus the undo file provides the extra scaffolding needed to handle this unusual arrangement.

    It's not about "dirty" pages, that's handled properly by the WAL protocol (write-ahead logging) and will be properly handled by recovery as operations are redone or undone.

    I would recommend reading up on how recovery works, as this is the crux of the issue.

    http://technet.microsoft.com/en-us/library/ms191455(v=SQL.105).aspx

    http://technet.microsoft.com/en-us/library/ms190440(v=sql.105).aspx

    Pay specific attention to the REDO and UNDO phases as well as the LOG CHAIN portion of the second link.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Sunday, November 30, 2014 1:55 AM
    Answerer
  • I don't think we're on the same 'page' (pardon the pun).  You say, 'Thus your "standby" database is no longer standby. Thus the extra file holding undone transactions that will be needed should further log restores occur. '

    You might be jumping ahead of me because, after all, when is a standby not a standby, as you suggest? Never, in the perspective of THIS thread. You seem to be aiming at a different perspective,  looking forward to full recovery (transitioning a standby to full readwrite status). That's not what I'm addressing here.


    I think the point I made is valid. There has to be  some difference between a standby database and a regular database. They can't be the same in ALL respects. Surely the undo file is needed due to this difference. What would that difference be? My theory, once again, is that a regular database permits dirty records (uncomitted trans) whereas a standby rolls them back, hence the need for the undo file to accomodate this distinction.

    I'll take your word for it that any database CAN have an undo file. Fine. However, a standby database is said to NEED that undo file. Why? Again, the only reason I can think of is the issue of the dirty records (uncomitted trans). That would seem to be the crux of the matter.  Anyone else care to chime in here?
    Sunday, November 30, 2014 3:06 AM
  • You might be jumping ahead of me because, after all, when is a standby not a standby, as you suggest? Never, in the perspective of THIS thread.

    It's not a standby database when it becomes read/write and the undo file no longer exists.

    I think the point I made is valid. There has to be some difference between a standby database and a regular database.

    The difference is that a standby database can still be recovered to a later point in time (it's read-only until then) and have more logs applied to it to roll it forward. A regular database cannot (as it's fully recovered).

    Surely the undo file is needed due to this difference.

    The undo file is *ONLY* needed to hold the transactions that would be UNDONE as part of UNOD RECOVERY. Thus the name.

    My theory, once again, is that a regular database permits dirty records (uncomitted trans) whereas a standby rolls them back, hence the need for the undo file to accomodate this distinction.

    The "regular" database is not in recovery, whereas the "standby" database is. See my earlier comment. You're confusing the point that a regular user database is not in recovery and can't have any successive restores done to it to roll it forward to a further point in time. This all has to deal with recovery and how it works, thus the links on the previous post.

    I'll take your word for it that any database CAN have an undo file. Fine.

    Any database that is being restored can in fact have an undo file using the STANDBY option. You're confusing a database that is fully recovered and up to be equal to a database that is in standby where it is not.

    http://msdn.microsoft.com/en-us/library/ms186858.aspx

    Again, the only reason I can think of is the issue of the dirty records (uncomitted trans).

    To make the database transactionally consistent, during the undo phase of recovery these transactions will be rolled back. That has nothing to do with it being a standby database, recovery works the SAME WAY everywhere. What makes the database a standby database (again) is that it is not fully recovered (WITH RECOVERY) and can have successive logs applied to it to roll it forward. Because it can be READ by clients, the database has to be transactionally consistent. This, however, poses a problem because when recovery is run to bring the database to a consistent state, the compensation records are going to take place as part of undo and the LSN chain will be broken. The undo file holds this portion of information for the database so that it can *still* be read *and* have other logs applied to it to roll it forward.

    Again, this has to do with recovery and not with uncommitted transactions.

    Edit:

    http://msdn.microsoft.com/en-us/library/ms178615.aspx

    Notice this is for *RESTORE*.

    "The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores."

    Again, this has EVERYTHING to do with RECOVERY as it's part of the RESTORING of a database.

    If there wasn't a standby file (putting the database in standby) then the database would either be in a RESTORING state (NORECOVERY) or it would be fully restored and open for business (WITH RECOVERY).

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.



    Sunday, November 30, 2014 3:43 AM
    Answerer
  • So, as you are aware, the Standby database only exists in a Log Shipping scenario.

    You are close with your own suggestion, but let me try to clarify things in simple terms ...

    In a Log Shipping scenario, the transaction log is backed up and then restored to the secondary database.  You need to also think of the restore as being a continuous process ... i.e don't think of it as a point in time snapshot of the database but as continuously replaying all the transactions through the log.

    Now, the log also contains "in-flight" transactions ... i.e. they have written into the transaction log but have not yet committed.  The may be part of a bigger UPDATE or DELETE statement which has a explicit transaction in it for example.  Until the COMMIT TRAN or ROLLBACK TRAN is issued, these are not hardened to the database (the mdf file) but still have to be logged in the ldf file.

    Now we add standby to the secondary database.   This allows us to read a transactionally consistent version of the database.  The only way it can be transactionally consistent is for the uncommitted transactions to be rolled back.   These transactions that are rolled back are stored in the Standby File.

    When the next tranaction log backup is restored, the standby portion is applied to get the restore chain back to the correct state and LSN, the next part of the transaction log is replayed on the secondary and then once again, any uncommitted transactions are rolled back and the details stored in the standby file to be used with the next restore.

    If the secondary is brought fully online, then the transactionally consistent state that it will be restored to is effectively everything except the stand by file - so the standby file is effectively discarded at this time, the latest LSN is set accordingly and the database is in a consistent state.

    Note - these are paraphrasing the processes that happen, not necessarily the actual step by steps, but I wanted to simplify the terms so you followed the reasoning.

    Hope that makes it clearer for you?

    Sunday, November 30, 2014 4:52 AM
    Answerer
  • Just to be clear:

    So, as you are aware, the Standby database only exists in a Log Shipping scenario.

    This is not true. A standby database can exist anywhere you can restore a database (except in SQL Azure).

    Example without using log shipping:

    USE master
    GO
    CREATE DATABASE StandByMe;
    GO
    
    ALTER DATABASE StandByMe SET RECOVERY FULL
    GO
    
    USE StandByMe
    GO
    
    BACKUP DATABASE StandByMe TO DISK = 'Z:\Backups\StandByMe_Full.bak' WITH INIT, CHECKSUM
    GO
    
    CREATE TABLE RestorePointsStandby
    (
    DT					DATETIME NOT NULL DEFAULT(GETUTCDATE()),
    AdditionalMessage	VARCHAR(50) NOT NULL
    );
    GO
    
    INSERT INTO RestorePointsStandby(AdditionalMessage) VALUES ('Initial Creation.');
    GO
    
    BACKUP LOG StandByMe TO DISK = 'z:\Backups\StandByMe_Log_Initial.trn' WITH INIT, CHECKSUM
    GO
    
    INSERT INTO RestorePointsStandby(AdditionalMessage) VALUES ('The Second Entry.')
    GO
    
    BACKUP LOG StandByMe TO DISK = 'Z:\Backups\StandByMe_Log_Second.trn' WITH INIT, CHECKSUM
    GO
    
    ALTER DATABASE StandByMe SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    USE master
    GO
    
    RESTORE DATABASE StandByMe FROM DISK = 'Z:\Backups\StandByMe_Full.bak' WITH CHECKSUM, REPLACE, STANDBY = 'Z:\Backups\Standby.tuf'
    
    USE StandByMe
    GO
    
    -- Table wasn't created yet, so creep forward
    SELECT * FROM dbo.RestorePointsStandby
    
    USE master
    GO
    
    RESTORE LOG StandByMe FROM DISK = 'Z:\Backups\StandByMe_Log_Initial.trn' WITH CHECKSUM, STANDBY = 'Z:\Backups\Standby.tuf'
    
    USE StandByMe
    GO
    -- log rolled forward, we now have the initial data, still in standby
    SELECT * FROM dbo.RestorePointsStandby
    
    USE master
    GO
    
    RESTORE LOG StandByMe FROM DISK = 'Z:\Backups\StandByMe_Log_Second.trn' WITH CHECKSUM, STANDBY = 'Z:\Backups\Standby.tuf'
    
    USE StandByMe
    GO
    -- all the data is back, we want to recover
    SELECT * FROM dbo.RestorePointsStandby
    
    USE master
    
    -- Bring back to full recovery and end the restore process
    RESTORE DATABASE StandByMe WITH RECOVERY
    GO

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Sunday, November 30, 2014 5:13 AM
    Answerer
  • All your statements seem to confirm we're not on the same page. Again, you're talking about recovery, whereas I'm talking about a standby database that might NEVER be brought into a read-write/recovered state. Even the link you gave seems to suggest that these are two different topics, as it states, "A third alternative, WITH STANDBY, is outside the scope of this topic." So, again, what you're referring to seems to be 'outside the scope of this topic' (i.e. the topic of THIS thread).

    You write, "It's not a standby database when it becomes read/write and the undo file no longer exists."   Again, this has nothing to do with this thread. Again, for the purpose of this thread, a standby database REMAINS a standby database, thus it NEVER becoms read-write. Thought I was clear on that point. Evidently not.

    So in the perspective  of THIS thread(and perhaps this is how a standby server works), the undo file ALWAYS exists. And the question of this thread is, why? A regular database doesn't need a permanent undo file, so why does a standby database need one? Again, the only reason I can think of is that it has to do with the way dirty records are handled.

    'The "regular" database is not in recovery, whereas the "standby" database is.' Here you go again. I'm not talking about recovery.


    "You're confusing the point that a regular user database is not in recovery and can't have any successive restores done to it to roll it forward to a further point in time."  First, I'm not talking about recovery. Second, any recovering database can have successive restores, not just standby databases, so that doesn't explain why only standby databases, to the exclusion of all other databases, needs a permanent undo file. The core distinctive of a standby is the undo file, not the successive restores.

    I'm just trying to understand why a standby database is the only kind of database that NEEDS a permanent undo file, and so far the only theory I seem to come up with is that a standby database handles dirty records differently than a regular user database.
    Sunday, November 30, 2014 5:21 AM
  • Hello,

    All your statements seem to confirm we're not on the same page. Again, you're talking about recovery, whereas I'm talking about a standby database that might NEVER be brought into a read-write/recovered state. Even the link you gave seems to suggest that these are two different topics, as it states, "A third alternative, WITH STANDBY, is outside the scope of this topic." So, again, what you're referring to seems to be 'outside the scope of this topic' (i.e. the topic of THIS thread).

    I'm not trying to mince words, but the ONLY way that there can be a database in a standby state is through restoring/recovery. If you don't believe this to be true or believe that recovery is part of this conversation this is why you're not understanding why the files needs to exist.

    You write, "It's not a standby database when it becomes read/write and the undo file no longer exists." Again, this has nothing to do with this thread. Again, for the purpose of this thread, a standby database REMAINS a standby database, thus it NEVER becoms read-write. Thought I was clear on that point. Evidently not.

    That doesn't make a difference. You're not understanding all of the processes that are encompassed with a database in standby. Regardless of whether it ever comes out of standby or not is irrelevant, however the way that standby works is which is because of the recovery process. These aren't mutually exclusive as you seem to believe. Again, this is why you're not understanding.

    So in the perspective of THIS thread(and perhaps this is how a standby server works), the undo file ALWAYS exists.

    Ok. It always exists. That doesn't change anything.

    And the question of this thread is, why?

    Again, because it is needed for recovery to be run, the database to be consistent, the database to allow connections, users allowed to read data (read only), and still allow for future log restores. The links I posted tell you exactly what the undo file holds and how it is used.

    'The "regular" database is not in recovery, whereas the "standby" database is.' Here you go again. I'm not talking about recovery.

    Again, YOU might not be talking about recovery but that's the mechanism that is in play here. Again, whether you want to believe it or not, that's what's going on and why the file is needed.

    Second, any recovering database can have successive restores, not just standby databases, so that doesn't explain why only standby databases, to the exclusion of all other databases, needs a permanent undo file.

    Correct, but no *in recovery* databases can be accessed by users to run queries. Thus the undo file is needed to make the database consistent while allow successive restores to happen. Again, it has to do with recovery, which you can continue to dismiss but is still the mechanism in use. I've even posted a code example to show this.

    I'm just trying to understand why a standby database is the only kind of database that NEEDS a permanent undo file, and so far the only theory I seem to come up with is that a standby database handles dirty records differently than a regular user database.

    Again, read the links I posted. They tell you EXACTLY WHY the file is needed. You, dismissing them because they deal with restores and recovery are the reason you're not understanding. Your theory is incorrect and the correct reason is documented. If you don't read it and choose to continue to not believe the facts presented with actual data behind them then you won't get very far.

    I'm done participating in this thread. Good luck.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Sunday, November 30, 2014 5:41 AM
    Answerer
  • "Again, because it is needed for recovery to be run, the database to be consistent, the database to allow connections, users allowed to read data (read only), and still allow for future log restores. The links I posted tell you exactly what the undo file holds and how it is used."

    But all these 'needs' also apply to non-standby recoveries so, again, you're not explaining why the undo file is needed ONLY for standby databases.

    The difference seems to be this. (As you say, let's not mince words, so I will use the word 'recovery' here in reference to the commonalities of standby and recovery). With ordinary recovery, the rollback of dirty records only occurs once (at the very end of the process) - with no possibility of a redo. Those proposed changes are gone, forever.

    Whereas with standby-recovery (as I've consented to the word 'recovery' here to avoid mincing words), the rollback of dirty records occurs ON EVERY RESTORE (that's the first difference) and with provision made for a possible redo so the changes aren't gone forever (that's the second difference). Since the proposed changes are still relevant (in a sense 'still pending' to speak very loosely/imprecisely), the  undo file is needed to record them so they are not forever lost.

    As I see nothing wrong with this analysis, I will soon mark this post as answer unless someone else quickly jumps in with a more persuasive one.

    • Marked as answer by jal2 Sunday, November 30, 2014 2:38 PM
    Sunday, November 30, 2014 6:02 AM
  • Here's my take on the subject. My apologies if I repeat anything already written in this thread:

    There's no such thing as a "standby database". A database can be in standby *state* if restored using the STANDBY option of the restore command. This is when the undo file is created. This is not exclusive to log shipping, but log shipping utilizes this.

    Above is fact. If you don't agree, then the rest of the discussion falls and becomes meaningless.

    You have three options for the RESTORE command. These controls whether UNDO is performed during restore. REDO is always performed. (A regular database start, as you probably know, consists of REDO and then UNDO.)

    RECOVERY. Both REDO and UNDO is performed. The database is in normal state ("read/write", "production") after restore. No further restore operations (differential, log) are allowed.

    NORECOVERY. Only REDO is performed. Database is not accessible, it is in "restoring" state. Subsequent backups (differential, log) can be restored.

    STANDBY. This is the one we are discussing. Both REDO and UNDO is restored. Database is in a special state, "standby/readonly/restoring" we can call it. UNDO was performed so we can read (only) in the database. But information for the REDO phase was saved in the undo file, so that SQL Server can undo the undo performed, allowing you to restore further backups. Please read the last sentence again, it is the key to understand the standby thingie.

    You can bring a database out of the "standby/readonly/restoring" state by executing the command "RESTORE DATABASE dbname WITH RECOVERY".

    The undo file is only used and required if you are to restore any subsequent backups (differential, log), where it will be used to undo the previously performed undo.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, December 3, 2014 4:44 PM
  • I was asked this question about 'what is TUF file' a few days ago, and as I (and i would think most are not using log shipping these days , with AG now in the picture), said what i remember is that it was used in log shipping when you want the other side to be read only etc , in the ballpark was my answer, but i also understood that my answer was not really complete and it set me to look at the answer if i could give it some time later on, well i did try googling it after some time and really had to spent some time as no-one was really going deep into the subject and there were pretty much 1 line answers. This post really helped me understand the answer COMPLETELY (maybe not completely but i definitely know more than before, i mean who knows things absolute completely :) ) what i liked the most was that even tho the original asker was sticking to his guns - which i liked- he was trying to understand .. there wasn't any personal malice from the answerer's , which personally on the posts that i see online are far and few in between , answerers on the this post came back and replied.. which i liked, and this helped me (or any other reader) understand the situation, everyone did a good job , but what stuck with me was the last post by Tibor , specifically 'undo the undo' - that's pretty much the answer right there and that's how i remember this answer these days, as with most things in sql its pretty much a rabbit hole, to really understand one needs to understand how t-log works , what is recovery and so on & on.. (there is a typo also on Tabor's post tho - 'But information for the REDO phase was saved in the undo file' -> word 'redo' needs to be undo there, but its all good.

    Overall great post for deep understanding of a topic. Thanks Sean,Martin & Tibor and also Jai to bring it up and to stick to his guns when many would have just vanished , he stuck on this post and it helped someone like me to better understand the topic.



    D


    • Edited by SQLRocker Sunday, August 16, 2020 4:59 AM
    Sunday, August 16, 2020 4:33 AM