Performance impact of lots of VLFs in the transaction log

One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted some performance numbers comparing insert/update/delete performance between a database with 16 VLFs and one with 20000 VLFs, representing a poorly managed transaction log. You can see his blog post here (2018: sadly it’s been taken down – sorry!)

For those wishing to take charge of unruly transaction logs, checkout Kimberly’s blog posts – starting with this one. And if you want to quickly know how many VLFs your transaction log has, use the undocumented DBCC LOGINFO command – the number of lines of output is the number of VLFs you have.

Next posts coming up will be photos – we’re in Bangkok right now and I’ve got 3 Where In The World Are Paul and Kimberly posts queued up – St. Lucia, Hyderabad, and Bangkok.

7 thoughts on “Performance impact of lots of VLFs in the transaction log

  1. Hi Paul,

    I’ve been reading up on log files and got on a tangent about VLFs and your caveat about having too many VLFs. (I also noted the the bug regarding log file growth in multiples of 4GB.) I went through the steps of reorganizing the log on one of our databases, and I ran into the wall trying to resize those very first VLFs resulting from the initialization of the logfiles. It seems that these are untouchable: you can’t remove the “primary” log file even if you have nothing in it (all VLFs have status = 0), and you have another logfile configured and currently taking log entries. If you use CREATE DATABASE FOR ATTACH_REBUILD_LOG, you get a default log file size that results in even smaller VLFs.

    Ok, I know this really isn’t significant, but I’m curious and you’re the god of the undocumented. Have you ever run across a way to reconfigure the original VLFs? Any way to move the “primary” status to another log file?

    Have a great day and good luck with shedding those pesky extra pounds.

    Bill

    PS – Any news on DevCOnnections in May?

    1. Hey Bill – there’s no way to change the PRIMARY status of the first log file, and you’ve found the only way to make the first VLF change size. You’re always stuck with the first log file created, with at least one VLF in it. News on Dev Connections replacement will be coming tomorrow. Cheers

  2. Can too few vlfs in database create performance issue? I have approx 1TB of log file and only 48 vlfs available on this database and some vlfs are more than 50 GB in size. Thank you..

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.