Blog

Fixing High VLF Counts

A couple weeks ago, my colleague, Brandi Dollar, wrote a blog post about SQL Server transaction log basics. Her post is a great lead-in to a script that I wrote to solve a common problem, high VLF counts. An important piece of managing your database transaction logs is keeping the number of virtual partitions within the log file, the Virtual Log Files (VLF for short), low. A high VLF count is typically a result of running with the default auto grow settings. As the transaction file continues to grow at sub-optimal levels, the fragmentation will become worse and worse. High VLF counts can have an impact on several performance issues.

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

When I log into a new server environment for a client, it is pretty common to see multiple databases that have high VLF counts. The steps above are easy enough to complete when you have one or two databases that need fixed, but it becomes a very tedious process when there are 20, 30, 40 databases that all need fixed.

To save a lot of the leg work involved, I wrote a script to generate all of the necessary commands. When you run the script below, it will print out all of the commands necessary to reduce the VLF count. Take the output from the messages tab, and you can run this result in phases in a new query window. I probably could have taken the script step a step further to completely automate the process; however, I still like to have control over the process and monitor the completion. Also, rather than writing my own transaction log backups, I will typically run an existing maintenance plan job as needed.

The output of the scripts below will come out similar to:

/****************************************
 * Fix High VLF for database: MattWorks
 * Starting VLF count: 219
 ****************************************/
 -- 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.

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.