Post reply

Truncating log file vs Checkpoint

  • August 23, 2011 at 10:06 pm

    #242749

    Hi,

    As per my knowledge when checkpoint occurs it marks VLFs from active to inactive and it also flush dirty pages to datafile. And at the time of truncating log file it marks those inactive VLFs to be reused, so those VLFs can be reused further. Please correct me if I am wrong.

    Thanks in advance.

  • August 23, 2011 at 10:20 pm

    #1372337

    This article should help you understand it.

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

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 24, 2011 at 1:28 am

    #1372380

    Truncating the log is the process of marking 0 or more VLFs as inactive. The log is truncated by a checkpoint in simple recovery and a log backup in full recovery.

    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

  • August 24, 2011 at 6:15 am

    #1372498

    And Checkpoint flush dirty buffer to disk and mark VLFs to reusable for further use. Please correct me if I am wrong.

    Thanks in advance.

  • August 24, 2011 at 6:45 am

    #1372509

    In simple recovery a checkpoint writes all dirty pages to disk and truncates the log. In full and bulk-recovery it just writes dirty pages to disk.

    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

  • August 24, 2011 at 7:16 am

    #1372534

    Ok, then how log file gets truncated in Bulk and Full recovery mode ? After every log backup or any other situation when truncate occurs.

    Thanks in advance.

  • August 24, 2011 at 7:20 am

    #1372540

    Yes and yes. There are certain commands that will truncate the log without backing it up. I suggest you go to the link that Jason posted - I'd be surprised if it doesn't answer all that and more.

    John

  • August 24, 2011 at 7:22 am

    #1372543

    GilaMonster (8/24/2011)

    The log is truncated by a checkpoint in simple recovery and a log backup in full recovery.

    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

  • August 24, 2011 at 7:08 pm

    #1372916

    Thanks all for answering my queries.

  • August 25, 2011 at 9:28 am

    #1373254

    you're welcome

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

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