Virtual Log Files (VLFs) can become problematic if not properly managed. Given the right circumstances, VLF counts can spiral out of control and lead to serious performance issues in your environment. The goal of this post is to provide a high-level overview of VLFs, steps to monitor and manage them using SentryOne and ways to prevent them using best practices.
What are VLFs?
Ever seen the movie Inception? If not, it involves a lot of dream within a dream type of stuff. Virtual Log Files are similar in that they are smaller files within a larger file (your Transaction Log). SQL Server has an easier time managing these smaller files as opposed to managing one large file. With every growth of a transaction log, SQL Server allocates space in the file using VLFs. There is a lot more to it than that, but this concept will cover the basics. For more in-depth reading click here.
Image of VLFs from Microsoft Technet
As previously mentioned, excessive VLFs can negatively impact all transaction log related activities. Simple tasks such as bringing a database online, performing a restore and running statements, such as INSERT and UPDATE can become taxing to your server. Performance degradation can even be felt during transaction log backups if VLF counts are high.
A common question asked around this time is, “What is considered a normal VLF count?”.
The answer to that question (and the favorite answer among DBAs) is, “It depends”, but a good rule of thumb is 45-50. Once your VLF counts start to climb into the several hundred range, you need to make changes. This is also an appropriate time to quote Star Wars: Return of the Jedi “There’s too many of them!”.
What can I do about it?
To rectify high VLF counts in a database and prevent them from increasing:
- Backup the Transaction Log of the database
- Using DBCC SHRINKFILE, shrink the Transaction Log as much as possible, or until you are below your desired VLF threshold*.
- Expand the Transaction Log to its target size
- Set the Transaction Log autogrowth to 1 GB**
You should also defragment the disk(s) that are housing your Transaction Logs. This will require SQL Server to be offline, however, you should only have to perform this action one time (unless your files continue to grow and shrink frequently). Defragmentation of the disk(s) will remove disk file fragmentation of the Transaction Log.
When a database is in FULL recovery, ensure log backups are being taken frequently. If point-in-time recovery is not needed, ensure your database is in SIMPLE recovery (NOTE: although SQL Server automatically reclaims log space when in SIMPLE recovery, a long running transaction can cause the transaction log file to grow.)
Transaction Logs should be created to its desired amount at the time of creation, rather than being allowed to grow frequently overtime. Initial size is another “It depends” situation as many variables such as: recovery model, transaction sizes, table sizes and transaction log backup frequency should be considered. The rule-of-thumb consensus is as follows:
Anticipated size of Transaction Log
Another important point to stress: although SQL Server allows you to create multiple Transaction Log files for a single database, you should avoid doing so. Transaction Log files are used sequentially so adding multiple log files will never result in a performance gain. Adding a secondary log file should never be used as an alternative to correcting and preventing high VLF counts.
The SentryOne tool comes with a High VLF Count Advisory Condition built in. The default for this alert is 300 VLFs, however, this value can be changed to fit your specific needs. As discussed previously, correct setup of your environment will lead to fewer headaches down the road.
High VLF Count Advisory Condition
Although DBCC LOGINFO and DBCC SQLPERF(logspace) can be used together to help determine current VLF counts, SentryOne provides several reports that I find much more beneficial to proactive prevention of excessive VLF counts.
High VLFs – By Server
This report will provide you with a list of all databases that have an excessive amount of VLFs for a specified server. Helpful in determining where problems will occur soon.
(Reports > Performance Analysis > Disk/File Size > High VLFs – By Server)
SQL Server File Utilization - by Database
Running this report will allow you to visualize the growth of your Transaction Log files and what percent of the file is being used. Helpful for determining which Log files are experiencing growth and at what rate.
(Reports > Performance Analysis > Disk/File Size > SQL Server File Utilization – by Database)
SQL Server Transactions - Top Databases
This report makes it easy to tell which databases are highly transactional. Using this report in correlation with the File Utilization report should allow you to visualize a trend in your environment, backed up by the High VLFs report.
(Reports > Performance Analysis > Performance > SQL Server Transactions – Top Databases)
Hopefully this post has given you better insight into managing Virtual Log Files. Just like “Walkers” from The Walking Dead, too many can become problematic. Using best practices in your SQL Server environment, along with taking full advantage of the tools SentryOne provides, will help your databases running like a car from The Fast and the Furious (Okay, I’m done with the movie references).
Keep in mind, this is by no means an all-inclusive lesson on VLFs. The information I have provided here should be used as a guideline. Remember to test changes on Test or Dev servers before implementing in a Production environment.
*May require multiple executions, you should shrink a large Transaction Log in smaller “chunks”
**Depends on your environment, however, 1 GB is a generally solid value to use.