Fix High VLF for sqlsrv database

 Fixing a database with a high VLF count is a simple process:

1. Check the current size of the transaction log.
2. Backup the transaction log.
3. Shrink the transaction log to as close to 0 KB as possible.
4. Check that the VLF count is less than 50 (best if less than 10 at this point).
       a. If the VLF count is greater than 50, repeat steps 1 through 3.
       b. You may also have to check that there are no active transactions, etc. The log_reuse_wait_desc column in sys.databases will help identify why the log file cannot be shrunk.
5. Grow the transaction log back out to the original size

Step 1: Get current log file size

use [MattWorks]
 SELECT name AS [File Name] , physical_name AS [Physical 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], type_desc
 FROM sys.database_files;

Step 2: Take transaction log backup if needed, and then shrink the log file

DBCC SHRINKFILE (N'MattWorks_log' , 0, TRUNCATEONLY);

Step 3: Check if current VLF count is less than 50.
If not, take another log backup and repeat step #2 until VLF count is less than 50. check for active transactions, etc.

DBCC LogInfo;

Step 4: Grow log file back to original size

USE [master];
GO
ALTER DATABASE [MattWorks] MODIFY FILE (NAME = N'MattWorks_log', SIZE = 1024MB);
GO

I hope that this script can be of use to someone.

/**************************************
* Script to generate commands to reduce transaction logs
* with hig VLF counts.
* written by Matt Nelson, RDX
***************************************/
SET NOCOUNT ON
--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
declare @dbccloginfo table
(
--resourceid int, --need to add this column for SQL 2012
fileid tinyint,
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 + ''') WITH NO_INFOMSGS'
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert @vlfcounts
values(@dbname, @vlfs)
delete from @databases where dbname = @dbname
end
/*
Build data file info
*/
CREATE TABLE #DatabaseFiles
(
[database_name] [sysname] NOT NULL ,
[file_id] [int] NOT NULL ,
[file_guid] [uniqueidentifier] NULL ,
[type] [tinyint] NOT NULL ,
[type_desc] [nvarchar](60) NULL ,
[data_space_id] [int] NOT NULL ,
[name] [sysname] NOT NULL ,
[physical_name] [nvarchar](260) NOT NULL ,
[state] [tinyint] NULL ,
[state_desc] [nvarchar](60) NULL ,
[size] [int] NOT NULL ,
[max_size] [int] NOT NULL ,
[growth] [int] NOT NULL ,
[is_media_read_only] [bit] NOT NULL ,
[is_read_only] [bit] NOT NULL ,
[is_sparse] [bit] NOT NULL ,
[is_percent_growth] [bit] NOT NULL ,
[is_name_reserved] [bit] NOT NULL ,
[create_lsn] [numeric](25, 0) NULL ,
[drop_lsn] [numeric](25, 0) NULL ,
[read_only_lsn] [numeric](25, 0) NULL ,
[read_write_lsn] [numeric](25, 0) NULL ,
[differential_base_lsn] [numeric](25, 0) NULL ,
[differential_base_guid] [uniqueidentifier] NULL ,
[differential_base_time] [datetime] NULL ,
[redo_start_lsn] [numeric](25, 0) NULL ,
[redo_start_fork_guid] [uniqueidentifier] NULL ,
[redo_target_lsn] [numeric](25, 0) NULL ,
[redo_target_fork_guid] [uniqueidentifier] NULL ,
[backup_lsn] [numeric](25, 0) NULL
)
EXEC dbo.sp_MSforeachdb 'INSERT INTO #DatabaseFiles SELECT ''[?]'' AS database_name, * FROM [?].sys.database_files'
print ''
print '/*********************************************************************************************'
print 'Copy results below this line to new query window to execute as needed.'
print '*********************************************************************************************/'
DECLARE @databasename varchar(max)
DECLARE @vlfcount varchar(max)
DECLARE @filename varchar(max)
DECLARE @physicalname varchar(max)
DECLARE @totalsize varchar(max)
DECLARE @availablespace varchar(max)
DECLARE @typedesc varchar(max)
DECLARE dbcrsr CURSOR
FOR SELECT dbname, vlfcount
FROM @vlfcounts
WHERE vlfcount >50 --comment this line to print statements for all databases.
AND dbname not in ('tempdb','master','msdb','model','reportservertempdb')
ORDER BY dbname
OPEN dbcrsr
FETCH NEXT FROM dbcrsr INTO @databasename,@vlfcount
WHILE @@fetch_status = 0
BEGIN
DECLARE filecrsr CURSOR
FOR SELECT name, size/128
From #DatabaseFiles
Where database_name = '['+@databasename + ']' and type_desc='LOG'
OPEN filecrsr
FETCH NEXT FROM filecrsr INTO @physicalname, @totalsize
--Generate print statements to execute in new window
WHILE @@fetch_status = 0
BEGIN
if @totalsize=0 SET @totalsize=1
print char(10) + '/****************************************'
print '* Fix High VLF for database: ' + @databasename
print '* Starting VLF count: ' + @vlfcount
print '****************************************/'
print '-- Step 1: Get current log file size'
PRINT 'use [' + @databasename + ']'
print 'SELECT name AS [File Name] , physical_name AS [Physical 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], type_desc '
print 'FROM sys.database_files;'
print char(10) + '-- Step 2: Take transaction log backup if needed, and then shrink the log file' + char(10) + 'DBCC SHRINKFILE (N''' + @physicalname + ''' , 0, TRUNCATEONLY);'
print char(10) +'-- Step 3: Check if current VLF count is less than 50.'
print '-- If not, take another log backup and repeat step #2 until VLF count is less than 50. check for active transactions, etc.'
print 'DBCC LogInfo;'
print char(10)+'--Step 4: Grow log file back to original size'
print 'USE [master];' + char(10) + 'GO'
print 'ALTER DATABASE [' + @databasename + '] MODIFY FILE (NAME = N''' + @physicalname + ''', SIZE = ' + @totalsize + 'MB);' + char(10) + 'GO'
print +char(10)+char(10)+char(10)
FETCH NEXT FROM filecrsr INTO @physicalname, @totalsize
END
CLOSE filecrsr
DEALLOCATE filecrsr
FETCH NEXT FROM dbcrsr INTO @databasename,@vlfcount
END
CLOSE dbcrsr
DEALLOCATE dbcrsr
DROP TABLE #DatabaseFiles

Thank you for reading! Stay tuned for my next post in the coming weeks.