SQL Server Virtual Log Files
Each SQL Server database transaction log is composed of one or more physical files. Internal to each of these physical files are structures known as Virtual Log Files, or VLFs. Having too many or too few VLFs will impact the performance of your databases.
In this post I am just giving you a quick method to return the VLF count for each of your databases. I will come back in the near future to help you determine whether you have too many or too few VLFs, and to provide you a method for correcting them.
/*
VLF count retrieval -
Each transaction log file is divided logically into smaller segments called virtual log files. The number of VLFs in each database will grow based on the autogrowth settings for the log file, and how often transactions write to disk. Too many VLFs will slow your log backups, and can even slow down database recovery.
The VLF count is normal and expected in every database. Larger VLF counts, however, are an
impediment, and must be cleaned up.
This query returns the VLF count per database. */
USE master;
DECLARE
@query VARCHAR(100),
@dbname SYSNAME,
@filecount INT
-- Table variable
DECLARE @databases Table (dbname SYSNAME)
INSERT @databases (DBNAME)
SELECT name
FROM sys.databases
WHERE state = 0 -- << only online databases
-- Table variable for results
DECLARE @Results Table (DBNAME SYSNAME, VLFCount INT)
DECLARE @MajorVersion INT
SET @MajorVersion =
LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(max)))-1)
-- Table variable to capture DBCC loginfo output
IF @MajorVersion < 11 -- vSQL2012
BEGIN
DECLARE @dbccloginfoA table
(
FileID tinyint,
FileSize bigint,
StartOffset bigint,
FSeqno int,
[Status] tinyint,
Parity tinyint,
CreateLSN numeric(25,0)
)
WHILE EXISTS(SELECT TOP 1 DBNAME FROM @databases)
BEGIN
SET @dbname = (SELECT TOP 1 DBNAME FROM @databases)
SET @query = 'DBCC LOGINFO (' + '''' + @dbname + ''') '
INSERT @dbccloginfoA (FileID,FileSize,StartOffset,FSeqno,Status,Parity,CreateLSN)
EXEC (@query)
SET @filecount = @@rowcount
INSERT @Results (DBNAME,VLFCount)
VALUES (@DBNAME, @filecount)
DELETE @databases
WHERE dbname = @dbname
END
END
ELSE
BEGIN
DECLARE @dbccloginfoB TABLE
(
RecoveryUnitID int,
FileID tinyint,
FileSize bigint,
StartOffset bigint,
FSeqno int,
[Status] tinyint,
Parity tinyint,
CreateLSN numeric(25,0)
)
WHILE EXISTS(SELECT TOP 1 DBNAME FROM @databases)
BEGIN
SET @dbname = (SELECT TOP 1 DBNAME FROM @databases)
SET @query = 'DBCC LOGINFO (' + '''' + @dbname + ''') '
INSERT @dbccloginfoB(RecoveryUnitId,FileID,FileSize,StartOffset,FSeqno,Status,Parity,CreateLSN)
EXEC (@query)
SET @filecount = @@rowcount
INSERT @Results
VALUES (@dbname, @filecount)
DELETE @databases WHERE dbname = @dbname
END
END
-- output results
SELECT
dbname [Database],
filecount [VLF Count]
FROM
@Results
ORDER BY
dbname
Take a look at each of these links for much more information regarding the VLF's:
Transaction Log Physical Architecture
High Virtual Log File (VLF) Count
Transaction Log VLFs – too many or too few?