Transaction Logs: Virtual Log File (VLF) Management

There are already a lot of articles about VLF management. I’ll only try to enrich and correct here.

What are VLFs and why do they matter?

I don’t want to get too deep into this, so much on the web already: Simply said: Each transaction log file is divided in Virtual Log Files. Too few VLFs are bad, but too much VLFs are also very bad. To many VLFs can result in bad performance (http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx).
The amount of VLFs can be checked using the following statement, for each VLF a row is returned.
dbcc loginfo(‘Databasenamehere’)

Log file growth and the amount of VLFs

Each time a transaction log grows (either automatic or manually), the part that is extended gets divided in VLFs. The amount of VLFs depends on the file growth.
Across the internet you’ll find the following information regarding how many VLFs are created in your transaction log file:
  • File growth < 64MB the new part will contain 4 VLFs
  • File growth >= 64MB and < 1GB the new part will contain 8 VLFs
  • File growth > 1GB the new part will contain 16 VLFs
However, this is not entirely correct. To start with, a file growth of 64MB will also have 4 VLFs, not 8 as read across the web (http://www.realworlddba.com/post/2011/06/07/Virtual-Log-Files-VLF-And-SQL-Server-Performance.aspx and http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/).
Also, as pointed out in many articles: keep an eye on the 4GB bug (http://sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx).
But there is more! I do have a few transaction log files which only have 2 or 3 VLFs, so how did that happen? The answer: there is a minimum VLF size, being 248KB. Rule number two: Transaction log files can’t be smaller than 512KB. A 512KB transaction log file will consist of two VLFs:
FileId     FileSize                StartOffset         FSeqNo                Status   Parity    CreateLSN
2              253952                  8192                       99           2              64           0
2              262144                  262144                  0              0              0              0
The first VLF is 248KB as the first 8KB of the transaction log file can’t be used by VLFs. The second VLF is 256KB. If you were to try growing the file to 760KB (or anything between 512KB and 760KB), it will grow the file to 760KB, creating an extra VLF of 248KB.
So the correct growth versus VLFs is:
  • File growth <= 248, the new part will be 248KB and have 1 VLF
  • File growth > 248KB and < 496KB, the new part will be the size you specify and have 1 VLF
  • File growth >= 496KB and < 1MB, the new part will be the size you specify; the amount of VLFs will depend on the growth size
  • File growth >= 1MB and <= 64MB the new part will contain 4 VLFs
  • File growth > 64MB and <= 1GB the new part will contain 8 VLFs
  • File growth > 1GB the new part will contain 16 VLFs

Fixing

After reading about VLFs (in this article, but also read the ones I link to) you know how to check the amount of VLFs and you know why to keep it low. Only thing lacking is a script to fix and prevent it. I saw some scripts “out there”, but those were not ideal in my situation (or were not doing what was promised).
Warning: This version of the SP breaks your backup-chain when you’re using the full recovery model.
So I’ve created a SP which resizes the transaction log to have between 52 and 64 VLFs. It looks up the closes log size in the table below. If the @newSizeMB parameter is used that value is looked up, otherwise the current log size is used.
LogSize_MB
Num_VLFs
Growth_MB
13
52
1
26
52
2
52
52
4
104
52
8
208
52
16
416
52
32
780
52
60
896
56
128
1792
56
256
3584
56
512
4900
56
700
7000
56
1000
8000
64
2000
12000
64
3000
16000
64
4000
20000
64
5000
24000
64
6000
28000
64
7000
32000
64
8000
 You might want to adjust this with bigger transaction logs.
Some of the usage:
exec dbo.spResizeTlog@modus=‘view’
exec dbo.spResizeTlog@modus=‘view’,@database=‘databasehere’, @newSizeMB=2048
exec dbo.spResizeTlog@modus=‘generate’,@database=‘databasehere’
exec dbo.spResizeTlog@modus=‘execute’,@database=‘databasehere’

Of course you’ll have to test this SP before using it in production.

Below the code: (CODE UPDATED SINCE 2011/09/14 9:45 CET)

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIEROFF
GO
CREATEProcedure [dbo].[spResizeTlog]
(@modus varchar(50)=‘view’
, @database nvarchar(128)=NULL
, @newsizeMB int=NULL
, @maxautomaticgrowthMB int=NULL
)
AS
setnocounton
–Create a lookup table, to lookup the required growth size
declare @maxgrowth int
declare @maxVLFs int
declare @lookup table (size int, VLFs int, fullsize int, fullVLFs int, numofgrowths int)
insert @lookup(size, VLFs)values (1, 4)
insert @lookup(size, VLFs)values (2, 4)
insert @lookup(size, VLFs)values (4, 4)
insert @lookup(size, VLFs)values (8, 4)
insert @lookup(size, VLFs)values (16, 4)
insert @lookup(size, VLFs)values (32, 4)
insert @lookup(size, VLFs)values (60, 4)
insert @lookup(size, VLFs)values (128, 8)
insert @lookup(size, VLFs)values (256, 8)
insert @lookup(size, VLFs)values (512, 8)
insert @lookup(size, VLFs)values (700, 8)
insert @lookup(size, VLFs)values (1000, 8)
insert @lookup(size, VLFs)values (2000, 16)
insert @lookup(size, VLFs)values (3000, 16)
insert @lookup(size, VLFs)values (4000, 16)
insert @lookup(size, VLFs)values (5000, 16)
insert @lookup(size, VLFs)values (6000, 16)
insert @lookup(size, VLFs)values (7000, 16)
insert @lookup(size, VLFs)values (8000, 16)
update @lookup set fullsize=((50/VLFs)+1)*size, fullVLFs=((50/VLFs)+1)*VLFs, numofgrowths=((50/VLFs)+1)
selecttop 1 @maxgrowth=size, @maxVLFs=VLFs from @lookup orderby size desc
–Create tmp table to hold the info of logfiles of all databases
createtable #dblogfiles(
dbname nvarchar(128)
, RecoveryModel nvarchar(128)
, logfilename nvarchar(128)
, physical_name nvarchar(2000)
, logfileid int
, logsizeMB int
, CurrentVLFs int
, CurrentGrowth int
, CurrentGrowthType smallint)
–Declare and fill table for database loop
declare @databases table (dbname nvarchar(128),recoverymodel nvarchar(128))
insertinto @databases
select name, recovery_model_desc fromsys.databases
–Declare table for DBCC loginfo output
CREATETABLE #dbcc_loginfo
        (
           fileid       TINYINT,
           file_size    BIGINT,
           start_offset BIGINT,
           FSeqNo       INT,
           [status]     TINYINT,
           parity       TINYINT,
           create_lsn   NUMERIC(25, 0)
        )
–Loop to gather all current data
declare @dbname nvarchar(128)
declare @recoverymodel nvarchar(128)
declare @cmd nvarchar(max)
declare @cmd2 nvarchar(max)
WHILEEXISTS(select*from @databases)
BEGIN
      selecttop 1 @dbname=dbname, @recoverymodel=recoverymodel from @databases
      set @cmd=
      insert into #dblogfiles
      SELECT DB_NAME()
      ,null
      ,name
      , physical_name
      ,file_id
    ,( size / 128 )
    ,NULL
    ,growth
    ,is_percent_growth
      FROM   sys.database_files
      WHERE  type_desc = ”log”
      ‘
      SET @CMD2=‘declare @cmd nvarchar(max)’
      SET @CMD2=@CMD2 +‘SET @cmd =”’+REPLACE(@cmd,””,”””)+””+char(13)+char(10)
      SET @CMD2=@CMD2 +‘Execute [‘+@dbname+‘].dbo.sp_executesql @cmd’
      exec (@cmd2)
      truncatetable #dbcc_loginfo
    SET @cmd =‘DBCC loginfo (‘+””+ @dbname +”’) WITH NO_INFOMSGS’
    INSERTINTO #dbcc_loginfo
    EXEC (@cmd)
      UPDATE l set CurrentVLFs=x.cnt, RecoveryModel=@recoverymodel
      FROM #dblogfiles l innerjoin 
      (SELECT fileid, cnt=COUNT(1)FROM #dbcc_loginfo GROUPBY fileid) x
      ON x.fileid=l.logfileid
      WHERE l.dbname=@dbname
     
      delete @databases where dbname=@dbname
END
select
dbname, logfilename, RecoveryModel, CurrentSizeMB=logsizeMB, CurrentNumOfVLFs=CurrentVLFs,
CurrentGrowth=CASEWHEN CurrentGrowthType=1 THEN CurrentGrowth ELSE CurrentGrowth/128 END
, CurrentGrowthType=CASEWHEN CurrentGrowthType=1 THEN‘%’ELSE‘MB’END
, RecommendedGrowthMB=ISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)
, RecommendedNewSize=ISNULL((SELECTTOP 1 fullsize FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT))))*@maxgrowth)
, RecommendedNumOfVLFs=ISNULL((SELECTTOP 1 fullVLFs FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT))))*@maxVLFs)
, RecommendedNumGrowths=ISNULL((SELECTTOP 1 numofgrowths FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize),((CEILING(CAST((ISNULL(@newsizeMB,logsizeMB))ASFLOAT)/CAST(@maxgrowth ASFLOAT)))))
, AutomaticGrowthSet=CASEWHENISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)> @maxautomaticgrowthMB THEN @maxautomaticgrowthMB ELSEISNULL((SELECTTOP 1 size FROM @lookup WHERE fullsize >=(ISNULL(@newsizeMB,logsizeMB))Orderby fullsize), @maxgrowth)END
into #result
from #dblogfiles orderby dbname
select*
,IsDiff=CASE
WHEN CurrentSizeMB<>RecommendedNewSize THEN 1
WHEN CurrentGrowthType=‘%’THEN 1
WHEN CurrentGrowth<>AutomaticGrowthSet THEN 1
WHEN CurrentNumOfVLFs >(RecommendedNumOfVLFs + 30)THEN 1
ELSE 0
END
into #result_generate
from #result
droptable #dblogfiles
droptable #dbcc_loginfo
IF @modus=‘view’
BEGIN
      select*from #result_generate where dbname=isnull(@database, dbname)orderby dbname
END
ELSE
BEGIN
      –modus must be generate or execute, so we need to generate the code
      DECLARE @logfilename nvarchar(128)
      DECLARE @GrowthMB int
      DECLARE @Growths int
      DECLARE @AutomaticGrowthSet int
      declare @shrink_command nvarchar(max)
      declare @alter_command nvarchar(max)
      WHILEEXISTS(select*from #result_generate where dbname=isnull(@database, dbname)and IsDiff=1)
      BEGIN
            SELECTTOP 1 @dbname=dbname, @logfilename=logfilename,@recoverymodel=recoverymodel, @GrowthMB=RecommendedGrowthMB,@Growths=RecommendedNumGrowths, @AutomaticGrowthSet=AutomaticGrowthSet  
            FROM #result_generate where dbname=isnull(@database, dbname)  and IsDiff=1 ORDERBY dbname
            SET @alter_command=
            IF @recoverymodel<>‘SIMPLE’
            BEGIN
                  SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY SIMPLE;’
            END
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘DBCC SHRINKFILE (N”’+ @logfilename +
                                                 ”’ , 0, TRUNCATEONLY);’;
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘DBCC SHRINKFILE (N”’+ @logfilename +”’ , 0);’;
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE ( NAME = N”’+ @logfilename +”’, MAXSIZE = UNLIMITED, FILEGROWTH = ‘+
            CAST(@AutomaticGrowthSet ASNVARCHAR)+‘ MB );’
            IF @recoverymodel<>‘SIMPLE’
            BEGIN
                  SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY ‘  + @recoverymodel +‘;’
            END
           
            –Do this twice, to get the file as small as possible
            IF @recoverymodel<>‘SIMPLE’
            BEGIN
                  SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY SIMPLE;’
            END
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘DBCC SHRINKFILE (N”’+ @logfilename +
                                                 ”’ , 0, TRUNCATEONLY);’;
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘DBCC SHRINKFILE (N”’+ @logfilename +”’ , 0);’;
            SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE ( NAME = N”’+ @logfilename +”’, MAXSIZE = UNLIMITED, FILEGROWTH = ‘+
            CAST(@AutomaticGrowthSet ASNVARCHAR)+‘ MB );’
            IF @recoverymodel<>‘SIMPLE’
            BEGIN
                  SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘ALTER DATABASE [‘+ @dbname +‘] SET RECOVERY ‘  + @recoverymodel +‘;’
            END
           
            DECLARE @Cntr int
            SET @Cntr = 0
            WHILE @Cntr < @Growths
              BEGIN
                    SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+
‘IF EXISTS(select * from sys.database_files where type_desc=”LOG” and name=”’+@logfilename+”’ and size/128 < ‘+CAST(((@GrowthMB )*( @Cntr + 1 ))ASNVARCHAR)+‘)
BEGIN
      ALTER DATABASE [‘+ @dbname +‘] MODIFY FILE (NAME = N”’+@logfilename+”’, SIZE = ‘+CAST(((@GrowthMB )*( @Cntr + 1 ))ASNVARCHAR)+‘MB)
END;’
                    SET @Cntr = @Cntr + 1;
              END 
                  IF @modus=‘generate’
                  BEGIN
                        SET @alter_command=‘USE [‘+ @dbname +‘];’  +CHAR(13)+CHAR(10)+@alter_command 
                        SET @alter_command=@alter_command +CHAR(13)+CHAR(10)+  ‘GO’
                        PRINT @alter_command
                  END
                 
                  IF @modus=‘execute’
                  BEGIN
                        SET @CMD2=‘declare @cmd nvarchar(max)’
                        SET @CMD2=@CMD2 +‘SET @cmd =”’+REPLACE(@alter_command,””,”””)+””+char(13)+char(10)
                        SET @CMD2=@CMD2 +‘Execute [‘+@dbname+‘].dbo.sp_executesql @cmd’
                        exec (@cmd2)
                  END
              DELETE #result_generate where dbname=@dbname and logfilename=@logfilename
      END
END
droptable #result
droptable #result_generate
GO

 

About these ads

About sqlsmurf
MS SQL Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: