Proc to Fix the Too Many Virtual Log Files Problem

There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.

If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here.

VLFs: A Quick Overview

What we think of as a log file is actually a bunch of interconnected of disk space chunks called VLFs. A log file can consist of a few, or hundreds, or even thousands of VLFs, and a new one is created every time the log file needs to expand. In a perfect world, you could create your log file to be the biggest it would ever need to be; since the log file would never need to expand, there would be no new VLFs created. Problem solved.

Unfortunately, if you're reading this, you don't live in a perfect world. Very likely, you're responsible for databases that have too many VLFs, and having too many VLFs is bad, especially for performance. So, your first step is to check your databases' log files to see how many VLFs they have. Then, if they have too many VLFs, you can fix the problem with a few simple commands. Fortunately for you, I've created a stored procedure that does both steps, so you can spend your time on more interesting things.

(How many VLFs is "too many"? It depends. In this blog, our bias is toward providing tools to get things done; I leave theoretical questions to people far more qualified. I picked "50" because Kimberly said so, and that's good enough for me.)

The Weapon

The script below will create a stored procedure that will examine and, if necessary, fix, the VLF problem in all the databases in your instance. Usually, you'll only need to run this once in a very long while, but again, it depends. Note the "@ExecuteFix" argument - setting this flag to zero allows you to see what T-SQL code would be executed; setting it to one actually executes that T-SQL.

-- Drop if it already exists. 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_FixExcessiveLogFileVlfs]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------------------
-- Display, logs, and fixes all databases with more than 50 Virtual Log Files. 
--                                                                             
-- See: http://www.simple-talk.com/sql/database-administration/sql-server-transaction-log-fragmentation-a-primer/
-- See: http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-transaction-log-throughput.aspx
-- See: http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx
--                                                                             
-- Created 2012-04-28 - Larry Leonard - http://SqlSoundings.blogspot.com/      
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[dba_FixExcessiveLogFileVlfs]  @ExecuteFix INT = 0
AS
BEGIN
    SET NOCOUNT ON

    -- Set up temp tables. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage
        
    CREATE TABLE #stage
    (
        FileID          INT
      , FileSizeBytes   BIGINT
      , StartOffset     BIGINT
      , FSeqNo          BIGINT
      , [Status]        BIGINT
      , Parity          BIGINT
      , CreateLSN       NUMERIC(38)
    )
     
    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results

    CREATE TABLE #results
    (
        DatabaseName    sysname
      , LogFileName     sysname
      , VlfCount        INT
      , LogFileSizeMB   INT 
    )
     
    -- Gather the log file information into the temp tables. 
    DECLARE @Sql NVARCHAR(MAX)

    SET @Sql = 'USE [?] '
             + ''
             + 'INSERT INTO #stage '
             + '  EXEC sp_executesql N''DBCC LOGINFO ([?])'' '
             + ''
             + 'INSERT INTO #results '
             + '    SELECT DB_NAME(), MIN(FILE_NAME(FileID)), COUNT(*), SUM(FileSizeBytes) / 1024 / 1024 '
             + '      FROM #stage '
             + ' '
             + 'TRUNCATE TABLE #stage '
             
    EXEC sp_msforeachdb @Sql

    -- Log the results. 
    DECLARE @DatabaseName   sysname
    DECLARE @LogFileName    sysname
    DECLARE @VlfCount       INT 
    DECLARE @LogFileSizeMB  INT

    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , VlfCount
             , LogFileSizeMB
          FROM #results
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        RAISERROR('Database: %25s  -  Virtual Log Files: %4d  -  Size: %5d MB', 10, 1, @DatabaseName, @VlfCount, @LogFileSizeMB) WITH NOWAIT, LOG
        FETCH NEXT FROM cur INTO @DatabaseName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Display the results. 
    RAISERROR(' ', 10, 1) WITH NOWAIT
    
    SELECT *
      FROM #results
     ORDER BY VlfCount DESC

    -- Fix the log files with too many VLFs.  We add two MB to the size because 
    -- ALTER DATABASE requires that we make the log larger.  Adding one doesn't 
    -- work, because of rounding issues when dividing, I think. 
    DECLARE cur CURSOR LOCAL FOR
        SELECT DatabaseName 
             , LogFileName
             , VlfCount
             , LogFileSizeMB
          FROM #results
         WHERE VlfCount > 50
      ORDER BY VlfCount DESC

    OPEN cur
    FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Sql = 'USE ' + @DatabaseName                                          + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'CHECKPOINT '                                                   + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'DBCC SHRINKFILE (' + @LogFileName + ', 0, TRUNCATEONLY) '      + CHAR(10)
                 + ' '                                                             + CHAR(10)
                 + 'ALTER DATABASE ' + @DatabaseName                               + CHAR(10)
                 + '   MODIFY FILE '                                               + CHAR(10)
                 + '( '                                                            + CHAR(10)
                 + '      NAME = ' + @LogFileName                                  + CHAR(10)
                 + '    , SIZE = ' + CAST(@LogFileSizeMB + 2 AS NVARCHAR) + ' MB ' + CHAR(10)
                 + ') '                                                            + CHAR(10)

        IF @ExecuteFix = 0
        BEGIN
            RAISERROR('-- Proposed T-SQL code for database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT, LOG
        END
        ELSE
        BEGIN
            RAISERROR('Processing database %20s: Log File: %10s', 10, 1, @DatabaseName, @LogFilename) WITH NOWAIT, LOG
            EXEC sp_executesql @Sql
        END
        
        FETCH NEXT FROM cur INTO @DatabaseName, @LogFileName, @VlfCount, @LogFileSizeMB
    END
          
    CLOSE cur
    DEALLOCATE cur

    -- Done. 
    IF OBJECT_ID('tempdb..#stage') IS NOT NULL
        DROP TABLE #stage

    IF OBJECT_ID('tempdb..#results') IS NOT NULL
        DROP TABLE #results
END

/* Testbed. 
 
 EXEC dbo.dba_FixExcessiveLogFileVlfs @ExecuteFix = 0 
 
 */

GO