LLI Wiki — My Digital Knowledge Base

Reduce the VLF count of a transaction log

Count the VLFs of each SQL Server database, inspect one log and reduce its VLF count with a log backup, truncation and resize.

A transaction log is split into logical sections called VLFs (virtual log files). A large number of VLFs slows some operations, such as recovery and log backups. This article gives the queries to count the VLFs of each database, find the affected databases, and reduce the log.

Count the VLFs of each online database

The script runs DBCC LOGINFO for each online database. The output of DBCC LOGINFO changed from SQL Server 2012 (version 11), so the script adapts the target table to the version.

--variables to hold each 'iteration'  
declare @query varchar(100)  
declare @dbname sysname  
declare @vlfs int  
  
--table variable used to 'loop' over databases  
declare @databases table (dbname sysname)  
insert into @databases  
--only choose online databases  
select name from sys.databases where state = 0  
  
--table variable to hold results  
declare @vlfcounts table  
    (dbname sysname,  
    vlfcount int)  
  
 
 
--table variable to capture DBCC loginfo output  
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version 
 
declare @MajorVersion tinyint  
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1) 
 
if @MajorVersion < 11 -- pre-SQL2012 
begin 
    declare @dbccloginfo table  
    (  
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn 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 into @dbccloginfo  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
else 
begin 
    declare @dbccloginfo2012 table  
    (  
        RecoveryUnitId int, 
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn 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 into @dbccloginfo2012  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
  
--output the full list  
select dbname, vlfcount  
from @vlfcounts  
order by dbname

Examine one database

Once the database is identified, list its files and its VLFs:

use AppDb
go
EXEC sp_helpfile; 
GO

DBCC LOGINFO
GO

To see the space used by each file of the current database, this query returns the size, the used and free space, and the autogrowth setting:

SELECT DB_NAME() AS [database_name], 
	name AS [file_name], 
	[type_desc],
	[physical_name],
	CONVERT(DECIMAL(10,2),SIZE/128.0) AS [current_size_MB], 
	CONVERT(DECIMAL(10,2),CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [space_used_MB],
	CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(SIZE/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [percent_used],
	CONVERT(DECIMAL(10,2),size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) AS [free_space_MB],
	CONVERT(DECIMAL(10,2),((SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)/(SIZE/128.0))*100) AS [percent_free],
	'By ' + CASE is_percent_growth
		WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
		WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' 
		ELSE '' END 
	  + CASE max_size 
		WHEN 0 THEN 'DISABLED' 
		WHEN -1 THEN ' Unrestricted' 
		ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END AS [auto_growth]
	FROM sys.database_files;

Reduce the log

The following steps back up the log, truncate it, then resize the log file to 1 GB. The logical name of the log file is returned by sp_helpfile.

USE AppDb
GO
CHECKPOINT
go
BACKUP LOG AppDb TO DISK='D:\Backup\AppDb_log.bak' WITH COMPRESSION
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AppDb_log,TRUNCATEONLY)
GO
ALTER DATABASE AppDb
MODIFY FILE
(NAME = AppDb_log,SIZE = 1GB)
GO
DBCC LOGINFO
GO

Notes

A log backup is only possible with a recovery model that keeps the log (FULL or BULK_LOGGED). Replace AppDb and AppDb_log with the database name and the logical name of its log file. The backup path is an example.

Mastodon LLIMastodon SocialFosstodon