Here is an error that popped up in the SQL Server error log today:
5/6/2016 10:10:10 AM spid22s Database [DatabaseName] has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken up into Virtual Log Files
After looking further into this server, the VLF count turned out to be around 163,000. That is certainly the highest VLF that I have ever seen.
My personal threshold for too many VLFs is usually around 200 with my preference to be less than 100 VLF files. Others may have their preferences, they will all be far less than 10,000, and certainly less than the 163,000 that I had the opportunity to see today.
Database Health Monitor has a check built in to the Quick Scan Report to warn you if there are any databases on the server with more than 250 VLFs. Also, based on a suggestion from a Database Health Monitor user, I have added a notice on the database overview page to show users the total VLF count for their database.
Reducing the number of VLFs
So if you have a high VLF count, how do you go about fixing it. To start with, I wouldn’t exactly follow the steps in the error message above. The way I usually clean it up is by doing the following during off hours, or times of slow loads:
- Look at the current size of the log file, and save that number.
- Check for the number of VLF’s in use, if there are many being used, then run a transaction log backup.
- Shrink the log file to as small as it can go, hopefully close to 1mb.
- Expand the log file back to the original size in chunks not greater than 15gb. If the log file was 70gb, I would first expand it to 15gb, then 30gb, 45gb, 60gb, and finally to 70gb.
- Check the sizing and total count of VLF files.
- Check the file growth settings, and adjust to something better than those that caused the original problem.
- Check back over the next several weeks to see how the files look.
Related Links:
- Database Health Monitor
- Quick Scan Report
- Visualizing Log File VLF Sizing
- More VLF info at SteveStedman.com
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Why is it better to increase their size in small chunks?
Jose, I guess the question here is what you mean by small.
You don’t want to increase your log file in sizes that will give you greater than 1gb VLF’s so 15gb growth divided by 16 VLFs per growth gives you just less than 1gb VLF files.
The goal is to have as few of VLFs as possible with non of the VLFs being greater than 1gb in size.
-Steve Stedman