Handling High Virtual Log Files (VLFs)

Today, I want to go over what Virtual Log Files are and how to handle them if you have too many in your databases.

A SQL Server log file is made up of smaller files called Virtual Log Files (VLFs). As the log file grows, so will the count of VLFs. I haven’t seen or heard of a calculation that can be worked out to determine how many VLFs you should have or how many is too many for a database. I’ve heard that you shouldn’t have more than a few hundred. I’ve also heard to not worry about VLFs until you break 1000. If you check your databases and have thousands in a database, I would say it’s best to get that count lowered whether you’re seeing issues yet or not.

Why Should You Care?

What you will notice if you have too many VLFs is that your database backups and restores may not be as speedy as you’d like them to be. If you’re having these symptoms, check your VLFs with this query which will return a row for each VLF:

DBCC LOGINFO(ExampleDB);

If we want to see counts for each database on the server, we can run this query:

SELECT db.name AS 'Database Name'
	,COUNT(li.database_id) AS 'VLF Count'
	,SUM(li.vlf_size_mb) AS 'VLF Size MB'
	,SUM(CAST(li.vlf_active AS INT)) AS 'Active VLF'
	,SUM(li.vlf_active * li.vlf_size_mb) AS 'Active VLF Size MB'
	,COUNT(li.database_id) - SUM(CAST(li.vlf_active AS INT)) AS 'Inactive VLF'
	,SUM(li.vlf_size_mb) - SUM(li.vlf_active * li.vlf_size_mb) AS 'Inactive VLF Size MB'
FROM sys.databases db
CROSS APPLY sys.dm_db_log_info(db.database_id) li
--WHERE db.name = DB_NAME()
GROUP BY db.name
ORDER BY COUNT(li.database_id) DESC;
GO

This will show some additional information beside VLF counts. If you only care about information for the database you’re running the query in, just uncomment the WHERE clause in the script.

Getting VLFs Under Control

You’ve checked your databases and found one with thousands of VLFs. To get this under control, we’ll need to shrink and regrow the log file. Before we take any action on our database, we will want to schedule some downtime because this type of log file work is a blocking operation.

For our example, we aren’t in too bad of shape but we have 317 VLFs that we want to reduce:

When we’re ready, we’ll want to start by shrinking the log file down as small as it will go.

DBCC SHRINKFILE (N'ExampleDB_log' , 0)
GO

Checking our VLF count again, we see that the shrink worked to reduce our VLF count:

Once we confirm that the VLF count is down, we’ll want to regrow the file back to around the same size it was originally so we don’t go right back to having the same high VLFs problem. We can modify the size of the log file:

ALTER DATABASE [ExampleDB] 
MODIFY FILE (NAME = N'ExampleDB_log', SIZE = 300MB);

And then we should be good to go:

Thanks for reading!