Virtual Log Files – a sometimes forgotten performance bottleneck

If the number of virtual log files (VLFs) for a given database gets too high, say more than 50 or so, then there will be a performance hit due to fragmentation.  Whenever the "auto-grow" for a log file kicks in, more VLFs are created.  The best prevention is to ensure that the auto-grow value is not set too low.  The default value is 10% in SQL 2005, 2008, 2008R2, and 2012.  Depending on how often you add data to your database, this value may be appropriate or way too low.  Also be careful of the initial size.  The default value is 1 Mbyte, which could be disastrous if it is not increased at database creation.

•          What is a log file?

–        guarantees data integrity of the database and for data recovery

–        it is what makes ACID, in part, possible

•          atomicity

•          concurrency

•          isolation level

•          durability

•          A database has at least one transaction log file

•          Each transaction log file is comprised of VLFs

–        no fixed size

–        no fixed number of files

•          Make sure initial size and auto-grow is set correctly

–        Try to minimize the number of times a files grows

•          Some growth is of course ok

•          To determine number of VLFs, run

DBCC LogInfo(DatabaseName)

•          50 or less is ok

•          over 50:  use SHRINKFILE to reduce the number of virtual log files.

BACKUP LOG databasename TO devicename

DBCC SHRINKFILE(TransactionLogLogicalFilename, TRUNCATEONLY)

ALTER DATABASE databasename

MODIFY FILE

(

      NAME = TransactionLogLogicalFilename

    , SIZE = NewTotalSize

)

Now, for the real power of using code to help with database administration.  Create a procedure that will store the VLF info for all databases.  You can schedule this to run daily, and even set up alerts to be notified if the number of VLFs exceeds a threshold value, say 50 or 100.

See next post for a handy view which will show how many VLFs per database.

USE Admin

GO

CREATE PROC dbo.PopLogInfo

      @ShowSql bit= 0

AS

/*    DATE        AUTHOR            REMARKS

      10/16/12    PPaiva            Initial creation.

     

      DESCRIPTION

            Truncates and populates LogInfo using DBCC LogInfo.

           

      DEBUG

            SELECT *

            FROM LogInfo

            -- Number of VLFs per database

            SELECT Server, DB, COUNT(*) Qty

            FROM LogInfo

            GROUP BY Server, DB

            ORDER BY 3 desc, 1, 2

            SELECT status, COUNT(*)

            FROM LogInfo

            GROUP BY status;

            SELECT TOP 5 *

            FROM vwServerDBFile

            DBCC LogInfo ('AmRept')

     

      USAGE

            PopLogInfo 1

            PopLogInfo

*/   

SET NOCOUNT ON

-- DROP TABLE dbo.LogInfo

IF OBJECT_ID('dbo.LogInfo') Is Null

      CREATE TABLE LogInfo(

            ID int NOT NULL IDENTITY(1, 1) CONSTRAINT pk_LogInfo PRIMARY KEY CLUSTERED,

            Server varchar(30) NULL,

            DB sysname NULL,

            FileID int NOT NULL ,

            MBytes As Convert(decimal(8, 2), (SizeBytes/1000000.)),

            StartOffsetBytes bigint NOT NULL,

            FSeqNo bigint NOT NULL,

            Status int NOT NULL,    -- can be 0 or 2.  0 = reusable, 2 = not reusable

            StatusDesc  AS (case when Status=(0) then 'Reusable' when Status=(2) then 'Not Reusable' else '(undefined)' end),

            Parity int NOT NULL,    -- can be 64 or 128.  Value switches upon reuse.

            CreateLSN Numeric(25, 0) NOT NULL,  -- 0 indicates an original file, when database was created.

            SizeBytes bigint NOT NULL,

            dbID int NULL,

            InsertDate datetime CONSTRAINT def_LogInfoInsertDate DEFAULT GetDate()

            )

SELECT name, database_id

INTO #DBs

FROM sys.databases

DECLARE @MaxDB sysname,

            @DB sysname,

            @dbID int,

            @s varchar(500)

           

SELECT  @MaxDB = MAX(Name),

            @DB = ''

FROM #DBs        

           

IF @ShowSql = 0

      TRUNCATE TABLE LogInfo       

     

     

WHILE @DB < @MaxDB

      BEGIN

            -- Get next DB

            SELECT @DB = Min(Name)

            FROM #DBs

            WHERE Name > @DB       

            -- Get corresponding dbID

            SELECT @dbID = database_id

            FROM #DBS

            WHERE Name = @DB

            SET @s = 'INSERT INTO LogInfo (FileID, SizeBytes, StartOffsetBytes, FSeqNo, Status, Parity, CreateLSN)

      Exec (''DBCC LogInfo(''''' + @DB + ''''')'')

UPDATE LogInfo

SET DB = ''' + @DB + ''',

      dbID = ' + Convert(varchar, @dbID) + '

WHERE DB Is Null

'

            IF @ShowSql = 1

                  Print @s

            ELSE

                  Exec(@s)         

     

     

      END

UPDATE LogInfo

SET Server = Convert(varchar(30), SERVERPROPERTY('ServerName'))

WHERE Server Is Null