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.