Fixing High VLFs (Virtual Log Files)

A colleague of mine recently published a blog post explaining Virtual Log Files (VLFs) and why it is important not to have too few or too many VLFs. As a follow-up, this article discusses how to reduce VLFs when they have gotten too far out of control. The process is much easier than you may think, and the benefits of performance improvement are well worth the effort.

In short, a high VLF count is typically the result of a transaction log growing at sub-optimal levels, usually because the auto-growth settings on the database are too low. For example, let’s say your database needs a large transaction log file of about 10G. Your auto growth settings for that database are set at something very low such as 64k. This means that transaction logs had to grow many, many times in order to get to 10G, thereby creating many VLFs. This can lead to fragmentation in the transaction log, longer recovery times, and performance bottlenecks.

Ideally, your databases should have a VLF count under 50. To check VLFs, run the following query and look for any databases with a VLF count over 50:

SELECT [name] AS DatabaseName,
	COUNT(l.database_id) AS VLFCount,
	SUM(vlf_size_mb) AS TransactionLogSize
FROM sys.databases s
	CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
ORDER BY 2 DESC

To eliminate fragmentation and improve performance, follow these simple steps to reduce VLFs:

1. Make note of the current size of the transaction log.
2. Back up the transaction log.
3. Shrink the transaction log to as close to 0k as possible.
4. Check the VLF count using the query above. If the VLF count is more than 50, repeat steps 1-3 until the count is below 50.
5. Grow the transaction log back to the original size:
   • Right-click the database name
   • Select Properties
   • Click on Files
   • Update the ‘Size (MB)’ field for the transaction log to the size noted in Step 1
   • Check the ‘Autogrowth’ field and make sure it is set large enough for future expansion of the transaction log (in our 10G example, the Autogrowth setting should be at least 256k)
   • Click OK
   • Alternatively, you can also grow the transaction log with the following SQL statement: ALTER DATABASE [databasename] MODIFY FILE ( NAME = N'transactionlogfilename', SIZE = 10240000KB)

Keeping on top of VLFs is a simple and quick process that will surely result in performance gains in your database. Be sure to make this part of your regular database maintenance checks going forward.

For more information or any questions about our SQL Server Virtual-DBA Services, please contact us!