Post reply

Simple Recovery Model

  • July 3, 2012 at 12:43 pm

    #263528

    Hello Masters,

    According to my knowledge, Log file in simple recovery model automatically truncated and that truncated space will be reused.

    If that is correct, My question is "What type of automatic process going on for truncation ? What factors it considering for truncation?"

    Please help me to understood.

    Thanks in advance,

    Jitendra

  • July 3, 2012 at 12:57 pm

    #1508437

    This article should help you understand:

    http://qa.sqlservercentral.com/articles/Administration/64582/

    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

  • July 3, 2012 at 1:17 pm

    #1508449

    Thanks a lot Jason for quick response,

    But the article dont have any detail explanation of that mechanism. I want more details that how it truncate the inactive part? What type of mechanism used behind it?

    Thanks,

    Jitendra

  • July 3, 2012 at 1:20 pm

    #1508451

    The truncate is performed via a checkpoint. Keep in mind that a truncate in the t-log does not free space to the OS. It is a mechanism that marks a VLF as inactive (status 0) and ready to be used when the log circles back around to that vlf.

    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

  • July 3, 2012 at 1:32 pm

    #1508458

    Thanks a lot Jason !

    And I got one more article from Stairways tab...."Stairway to Transaction Log Management in SQL Server" in our site itself which has some details explanation in chapters. Hope it might help me understand in details..

    Thanks again.

    Jitendra

  • July 3, 2012 at 1:59 pm

    #1508477

    Truncating the log is very much a misnomer. As Jason states, the log is broken into smaller virtual log files (VLF) which are sized depending upon the initial size of the log and any growth that might have taken place.

    When the log is written to it writes into one of these VLFs. Each time a transaction is closed and a checkpoint occurs that portion of the log is marked as being available for reuse. SQL writes to the log in a circular manner. If a VLF is not marked as available for reuse (due to a long running transaction for example) then when SQL cycles back round to write to that portion it will instead have to grow the log file and create an all new VLF to write to.


    Shamless self promotion - read my blog http://sirsql.net

Viewing 6 posts - 1 through 5 (of 5 total)

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