Backup - full recovery Model and log files

  • I am wondering if someone could please confirm my understanding about logs files in full recovery mode, please.

    My understanding is that when I take a SQL Full backup my log files are cleared out (NOT SHRUNK) and new trans log data is saved. At the end of each trans backup a marker is added to the File to signify the trans backup. This file "wraps around" if there is free space. If the file becomes full then it expands.

    Thanks

    Taggs

  • Taggs (11/9/2011)


    My understanding is that when I take a SQL Full backup my log files are cleared out (NOT SHRUNK) and new trans log data is saved.

    No. Full backups don't do anything to the log

    At the end of each trans backup a marker is added to the File to signify the trans backup.

    Err, no. Log backups truncate (mark as reusable) portions of the log file.

    This may help: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    Edit: Removed something that could be misconstrued.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hmm, I'm a little confused. Although the file still has the data (which I thought it had cleared) that portion is now reusable?

    The article says the following

    If a log backup now runs, the third and fourth VLFs (C and D) would be marked inactive and would be available for SQL to reuse them.

    Surely this is the same/simlar effect as empting the file out (without shrinking)??

    Also the document states that the file does wrap around (also indicated in the diagram.

    After a number of data modifications, the log has wrapped around and SQL has now reused the first VLF and is about to start reusing the second.

    I'm really sorry if I'm being thick!

    Taggs

    🙂

  • Taggs (11/9/2011)


    Hmm, I'm a little confused. Although the file still has the data (which I thought it had cleared) that portion is now reusable?

    Yup. Nothing's actually cleared when a log truncation occurs, nothing gets overwritten at the time. All that happens is that portions of the log are marked as 'can be overwritten'

    The article says the following

    Also the document states that the file does wrap around (also indicated in the diagram.

    I never stated that it didn't.

    What I said was that log backups don't just put a marker in the log to signal a log backup. They actually truncate the log. It's not the full backup that makes the log reusable, it's the log backups.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Many thanks. It all makes sense now.

    It's not the full backup that makes the log reusable, it's the log backups.

    Thank you for imparting your wisdom

    Taggs

    🙂

  • Hi, Gail.

    That's an excellent article. Surely a result of years of experience.

    Can I add a little related question to this topic?

    Article (11/9/2011)


    (...) Log backups are the one that most people know can prevent log reuse. In full and bulk-logged recovery models, VLFs cannot be marked as inactive until all the log records in that VLF have been backed up. (...)

    If a transaction is rolled back, will the VLF used by that transaction be set to inactive even without performing a log backup? Or it must be kept active until the log backup is made?

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • codebyo (11/9/2011)


    If a transaction is rolled back, will the VLF used by that transaction be set to inactive even without performing a log backup? Or it must be kept active until the log backup is made?

    In full and bulk-logged recovery the only thing that marks a VLF as inactive is a log backup.

    Imagine, if a transaction roll back did mark a VLF inactive, what would happen to all the other transaction log records in that VLF. They wouldn't eb backed up, would be lost on a log ship secondary. Not great for DR.

    Also, imagine if a transaction rolled back and the roll forward portion had already been written to the data file and the rollback marked the VLF as inactive, what would happen if the server crashed right after the rollback. On restart, crash recovery wouldn't be able to replay the rollback. That's a recipe for a suspect DB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/9/2011)


    In full and bulk-logged recovery the only thing that marks a VLF as inactive is a log backup.

    Imagine, if a transaction roll back did mark a VLF inactive, what would happen to all the other transaction log records in that VLF. They wouldn't eb backed up, would be lost on a log ship secondary. Not great for DR.

    Also, imagine if a transaction rolled back and the roll forward portion had already been written to the data file and the rollback marked the VLF as inactive, what would happen if the server crashed right after the rollback. On restart, crash recovery wouldn't be able to replay the rollback. That's a recipe for a suspect DB.

    I was failing to see that the sequence of events in a database is important.

    Actually I totally forgot about all that after reading about VLFs.

    Thank you for your detailed explanation.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply