Unmanaged Virtual Log Files (VLF) are one of the most frequent issues I find in my client’s environments. Since there are a lot of misconceptions on this topic, in this post I hope to explain what VLFs are, why they get created, simulate a VLF count growth, demonstrate how we can monitor it and more importantly, fix it.
For the sake of practicality and readability, I’ll attempt to make this complex SQL engine internal mechanism as simple and straight-forward as possible.
Why and how does VLF count grow?
Every time a log file grows, new VLFs are created, therefore the log file autogrowth setting, and how busy your database is will dictate the growth rate of active VLFs in your log file.
When investigating the reasons for high VLF counts, 9 out of 10 times I find it’s a combination of these 2 factors:
– Small autogrowth settings on active databases
– Infrequent log backup strategy
Note: The size of the autogrowth dictates the number of VLFs created at every growth. I’ll explain this in a separate post.
Impacts of having high VLF count
Bottom line: It degrades transaction log file performance!
Too many virtual log files can cause log backups and database recovery to slow down and in extreme cases, even affect general DML performance.
How to check VLF count?
If you are on SQL 2017, this great script (from Pinal Dave) will output the total VLF count as well as a breakdown of active vs inactive VLFs and its sizes
--Only works on SQL 2017
SELECT
name AS 'Database Name',
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM
sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY
[name], s.database_id
ORDER BY
'VLF Count' DESC;
If you are on SQL 2016 or earlier, the following script will return the VLF count for all databases in a given instance:
--For all versions of SQL
CREATE TABLE #VLFInfo (
RecoveryUnitID int, FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));
CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
EXEC sp_MSforeachdb N'Use [?];
INSERT INTO #VLFInfo
EXEC sp_executesql N''DBCC LOGINFO([?])'';
INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;
TRUNCATE TABLE #VLFInfo;'
SELECT DatabaseName, VLFCount
FROM #VLFCountResults
WHERE databasename = 'VLF_DB' --So only our demo DB is returned
ORDER BY VLFCount DESC
DROP TABLE #VLFInfo
DROP TABLE #VLFCountResults
How to fix the VLF count issue?
Step 1 – Truncate the log file (turn active VLFs into inactive) and the cleanest way to do that is through a log backup. Note: this step may have to be repeated, until the last active VLF is at beginning of the chain.
Step 2 – Shrink the file. This is where the VLF count is reduced.
Step 3 – Grow the file to an adequate size, according to the workload of your database.
Step 4 – Adjust the autogrowth setting to a reasonable number, based on your workload.
Demo
1. Create the test environment
--Create a test database, in our case VLF_DB
CREATE DATABASE [VLF_DB]
GO
--So we can quickly demonstrate and observe the VLF count increasing, we are setting the autogrowth to 1MB
ALTER DATABASE [VLF_DB]
MODIFY FILE
(NAME=VLF_DB_log, FILEGROWTH=1MB);
GO
--We will create a table with an inefficient column so it can bloat and autogrow faster
USE [VLF_DB]
go
CREATE TABLE tbl_Employee
(name char(8000))
GO
--Lets take a full back up, just so we can take log backups in future steps (make sure that you have a temp folder on C:\ or change the path)
BACKUP DATABASE VLF_DB TO DISK='C:\TEMP\VLF_DB.BAK'
GO
--Now we will create a Stored Procedure, just so we can quickly check the content of the log file without having to run all this code.
CREATE PROC usp_CheckLogFile
AS
--1.Check the Total Size and available Space in the log file
SELECT name AS [File Name] , size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files
where FILE_ID=2;
--2.Check the VLF count and the number of active and inactive VLFs
CREATE TABLE #VLFInfo (
RecoveryUnitID int, FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));
CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);
EXEC sp_MSforeachdb N'Use [?];
INSERT INTO #VLFInfo
EXEC sp_executesql N''DBCC LOGINFO([?])'';
INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;
TRUNCATE TABLE #VLFInfo;'
SELECT DatabaseName, VLFCount
FROM #VLFCountResults
WHERE databasename = 'VLF_DB' --So only our demo DB is returnedORDER BY VLFCount DESC
DROP TABLE #VLFInfo
DROP TABLE #VLFCountResults
--3.Take a look at the actual log file structure, the number of rows is the VLF count
DBCC loginfo
GO
2. Insert data to the table and watch the VLF count grow
--Run the Proc to check the inital status of the log file before the insert
EXEC usp_CheckLogFile
GO
--Now we will insert 10K rows and watch the number of VLFs increase
INSERT INTO tbl_Employee VALUES ('TEST')
GO 10000
--You can continuosly run our proc from a different window and notice the log file size going up as well as the number of VLFs with every file increment
GO
EXEC usp_CheckLogFile
3.Fix the issue
--Now that we have a DB with a large VLF count, lets bring it down.
--1. Run a Log backup (You hight have to run this a few times, check the 'Gotcha'at the end of the article)
BACKUP log VLF_DB TO DISK = 'C:\temp\log.bak'
GO
--1a. Run the proc again and note that the inactive VLFs will drasticaly increase as well as the amount of free space due to the truncation, but also note that the total VLF count is still the same.
exec usp_CheckLogFile
GO
--2. Shrink the file
DBCC SHRINKFILE ('VLF_DB_log',0)
GO
--2.a Check that the file has chrunk and that the VLF count is low
exec usp_CheckLogFile
GO
--3. Grow the file as well as the autogrowth to reasonable sizes according to your database's workload
ALTER DATABASE VLF_DB
MODIFY FILE
(NAME = VLF_DB_log, SIZE = 1GB, FILEGROWTH=500MB);
GO
There is a Gotcha!
If you run the DBCC shrink and the file size as well as the VLF count remains the same, your log backup didn’t fully truncate the log file because an active VLF was reached. You might have to run the Tlog backup a few times until the last active VLF (status = 2) is at the beginning of the chain. You can check that by running the procedure, scrolling to the last line and scan for status = 2. If you find any run another log backup until the only active VLFs are at the beginning of the chain sequence.
