Minimum Number of VLFs

  • Mike Hays

    SSCommitted

    Points: 1871

    Comments posted to this topic are about the item Minimum Number of VLFs

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files.

    Damn you Brent Ozar!

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

  • twin.devil

    SSC-Insane

    Points: 22208

    nice question to recall the basics 🙂

    thanks for sharing

  • Hany Helmy

    SSChampion

    Points: 13488

    Nice question, it`s always good to go back to basics.

    Also there is a good reference for implementing VLF`s:

    http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/

  • Michael Riemer

    SSCertifiable

    Points: 5115

    Great question thanks - had to research this one to make sure, which is always useful.

  • Ed Wagner

    SSC Guru

    Points: 286982

  • george sibbald

    SSC Guru

    Points: 104200

    I got this right this because I knew it was impossible to shrink to less than 2 VLFs.

    As the URL referred to in Hanys post hints at, if creating a log file don't create it too large to start with in case you need to shrink it. if you want it 8GB big (16VLFs), create at 64MB (4 VLFs) and then grow it to 8GB, you will then be able to shrink it very small if required.

    (normal caveats on not shrinking unnecessarily, blah,blah)

    ---------------------------------------------------------------------

  • TomThomson

    SSC Guru

    Points: 104773

    Good question. I learnt something.

    I picked 4, which was wrong, because I have neve seen fewer than 4. I guess I've never worked with a database that started out with less than a megabyte of log file.

    Tom

  • Rich Weissler

    Hall of Fame

    Points: 3235

    Picked four as well because of a performance blog I read years ago[/url], and the number four stuck in my head. Sounds like the correct answer is a special case... but is the actual minimum. Good to know.

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104773

    Stewart "Arturius" Campbell (11/27/2013)


    Interesting one, thanks Mike

    Had to research, even found Kimberly Tripp's blogs on this topic (8 Steps to better Transaction Log throughput

    [/url] and Transaction Log VLFs – too many or too few?[/url]).

    If I remember rightly Kimberly Tripp blogged that anything below 64MB as an initial allocation would be split into 4 VLFs, which wouldn't have led you to the right answer.

    Tom

  • Scott Arendt

    SSCertifiable

    Points: 7838

    Koen Verbeeck (11/26/2013)


    VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files.

    Damn you Brent Ozar!

    Ditto. I had no idea what the answer was so Google sent me to Brent Ozar. He's gotta know, right?

  • TaylorMade

    SSC Eights!

    Points: 966

    Scott Arendt (11/27/2013)


    Koen Verbeeck (11/26/2013)


    VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files.

    Damn you Brent Ozar!

    Ditto. I had no idea what the answer was so Google sent me to Brent Ozar. He's gotta know, right?

    Too funny, me too! http://www.sqlservercentral.com/Forums/Skins/Classic/Dialogs/InsertEmotIcon.aspx?ControlID=txtPost#

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Here's another interesting reference (this is the one that I found when researching the question):

    http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/[/url]


    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/

  • sqlnaive

    SSCoach

    Points: 17435

    Very good basic question. 🙂

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

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