Minimum Number of VLFs

Question

Post reply

Minimum Number of VLFs

Mike Hays

SSCommitted

Points: 1871
More actions
November 26, 2013 at 9:20 pm

#286867

Comments posted to this topic are about the item Minimum Number of VLFs
November 27, 2013 at 7:22 am

This was removed by the editor as SPAM

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

nice question to recall the basics 🙂

thanks for sharing

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

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/

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 4

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Good question - thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

george sibbald SSC Guru Points: 104210 More actions · Answer 6

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 More actions · Answer 7

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: 3286 More actions · Answer 8

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

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 SSCrazy Eights Points: 8218 More actions · Answer 10

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: 967 More actions · Answer 11

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: 64790 More actions · Answer 12

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: 17437 More actions · Answer 13

sqlnaive

SSCoach

Points: 17437

December 9, 2013 at 2:31 am

#1671834

Very good basic question. 🙂