Clearning the transaction log

  • Comments posted to this topic are about the item Clearning the transaction log

  • Nice easy question.

    It will be interesting to see how many manage to get it wrong.

    Tom

  • I got it wrong. But am surprised that when a database is set to simple recover model, it will either clear 0 or all vlf. Good to know this.

  • Another great VLF question!

    edit: there's a small typo in the question's title 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Excellent question. I didn't want to take a chance before taking the question. To be sure, I referred to this link which gave me an idea to answer correctly.

    http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx

    M&M

  • Tom.Thomson (2/3/2011)


    It will be interesting to see how many manage to get it wrong.

    And how many gripe about Steve's spelling :hehe:

    Easy question, though I'm concerned explanation is far too simple. There's more involved in this than just size of a VLF. But then, I have been writing an article on tran log reuse for the last 3+ weeks.

    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
  • Abi Chapagai (2/3/2011)


    But am surprised that when a database is set to simple recover model, it will either clear 0 or all vlf. Good to know this.

    It won't. It'll only clear VLFs that aren't needed for something - active transaction, DB backup, replication, etc

    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
  • Good question, but the explanation is overly simplistic. There are other reasons for a checkpoint to not clear a VLF.

    I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.

    And re: the spelling error in the title - I'll just go ahead and say that this is a deliberate word play on "clearing the transaction log" and "learning (about) the transaction log". 😀


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Good question Steve.

    Gethyn Elliswww.gethynellis.com

  • Hugo Kornelis (2/4/2011)

    I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.

    Similar thinking here...I thought immediately that a VLF can't be cleared if it's in use, and what guarantees they won't be in use for transactions at the time the checkpoint occurs?

  • paul.knibbs (2/4/2011)


    Hugo Kornelis (2/4/2011)

    I chose the correct answer because I considered the scenario of a database with a long-running transaction. When the oldest active VLF is necessary for tha ttransaction, no checkpoint can ever clear a VLF until the transaction is finished.

    Similar thinking here...I thought immediately that a VLF can't be cleared if it's in use, and what guarantees they won't be in use for transactions at the time the checkpoint occurs?

    Common theme, pretty much my thinking as well.

  • Hugo Kornelis (2/4/2011)


    There are other reasons for a checkpoint to not clear a VLF.

    I also selected the right answer for the "wrong" reason. :w00t:

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

    My new question today is does Tran Log Truncation or Check Points or Both get delayed by these situations?

    Does the Tran Log always get truncated after a checkpoint occurs, or does Datbase Engine try to remove the 0 or more VLF after the checkpoint occurs?

    😎

  • Great question, too simple of an explanation though..Paul's Blog is very helpful too...:-D

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Oops, hit Send too soon.

    Removed.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 23 total)

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