Always On

Please fill out our quick contact form to request more information about RDX.

Close

Always On

Always On

Please fill out our quick contact form to request more information about RDX.

Close

Always On

dba_image.jpg

Fixing High VLF Counts

No Comments »

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:

[code]
/****************************************
* 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
[/code]

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

[code]
/**************************************
* 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

[/code]

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

RDX Bloggers, DBA Tips

​Be sure to Choose
the Right Provider
Download our free guide

Important
Questions to Ask

when choosing a
Remote DBA Provider
This simple .pdf will help ensure you efficiently evaluate remote DBA companies
Download NOW!