HowTo: Grow a very large MS-SQL Log File

May 4, 2011 · Posted in SQL 

Microsoft SQL Server 2005 has a nasty habit of creating thousands or hundreds of thousands of logical log files even though you set Autogrowth to a large size, say 8GB. The following SQL code automates manually growing your log file by 8GB increments. When using the ALTER DATABASE command to modify log file size manually like this, SQL Server seems to properly create logical log files of around 1GB each resulting in far less logical log files overall. This should increase log file performance.

This code assumes several things:
    1) If your database has multiple log files, we will grow the most recently created log file.
    2) You have backed up the database AND log file immediately prior to running this code.
    3) If mirroring your database using SQL Mirroring that the principle and mirror databases are properly synchronized.

You will need to change 3 variables as noted in the comments at the top of the code; @DatabaseName, @BaseSize, and @TargetSize:
    @DatabaseName is, rather obviously, the name of the database whose log file needs growing
    @BaseSize is the current size of the logfile expressed in MB.
    @TargetSize is the desired size of the logfile, again expressed in MB.

Note Well: This code is to be used only at your own risk, and comes with no warranties, guarantees or otherwise, express or implied.


/*
    Procedure for reliably growing very large log files by 8GB increments
    8GB increments allows the database engine to create 1GB
    logical files within each 8GB physical logfile segment
    By Max Vernon, 2011-05-04

    Modify the @DatabaseName, @BaseSize, and @TargetSize variables
    below to suit your needs
*/

DECLARE @ExecString nvarchar(max);
DECLARE @DatabaseName nvarchar(255);
DECLARE @Response nvarchar(max);
/*
 NAME OF DATABASE WHOSE LOG FILE
 WE WANT TO GROW
*/
SET @DatabaseName = 'RemoteClients'; SET @ExecString = 'USE ' + @DatabaseName + ';
DECLARE @Mirroring bit;
DECLARE @LoopCount int;
DECLARE @BaseSize int;
DECLARE @TargetSize int;
DECLARE @Sql nvarchar(255);
DECLARE @LogFileName nvarchar(255);
DECLARE @NumLoops int;

/*
 EXISTING SIZE OF LOG IN MB
*/
SET @BaseSize = 4000; 
/*
 TARGET SIZE FOR LOG IN MB
*/
SET @TargetSize = 40000; 
SET @LoopCount = 0; 
/*
 LOOP ONCE FOR EACH 8GB IN LOG SIZE GROWTH 
*/
SET @NumLoops = (@TargetSize - @BaseSize) / 8192 

USE ' + @DatabaseName + ';

/* GROW THE MOST RECENTLY ADDED LOGFILE */
SET @LogFileName = 
    (SELECT TOP (1) [name] 
    FROM sys.database_files 
    WHERE type_desc = ''LOG'' 
    ORDER BY file_id DESC);

/*
 IF DATABASE IS MIRRORED, PAUSE MIRRORING WHILE GROWING
 DATABASE TO PREVENT UNNECCESSARY LOCKING 
*/
IF DB_ID(''' + @DatabaseName + ''') IN (
    SELECT database_id 
    FROM sys.database_mirroring 
    WHERE mirroring_guid IS NOT NULL AND mirroring_role = 1)
    SET @Mirroring = 1;
ELSE
    SET @Mirroring = 0;

IF @Mirroring = 1 /* PAUSE MIRRORING */
    ALTER DATABASE ' + @DatabaseName + ' SET PARTNER SUSPEND;

USE ' + @DatabaseName + ';

/*
 SHRINK LOGFILE TO INITIAL SIZE 
*/
DBCC SHRINKFILE (@LogFileName , @BaseSize); 

/*
 ONLY ATTEMPT GROWTH PROCEDURE IF LOG FILE WAS SHRUNK SUCCESSFULLY
 TO @BaseSize 
*/
IF (
    SELECT ([size] * 8)/1024 
    FROM sys.database_files 
    WHERE type_desc = ''LOG'' AND name = @LogFileName) = @BaseSize 
    BEGIN
        WHILE @LoopCount <= @NumLoops
        BEGIN
            SET @LoopCount = @LoopCount + 1;
            SET @Sql = ''ALTER DATABASE [' + @DatabaseName + '] MODIFY
 FILE ( NAME = '' + @LogFileName + '', SIZE = '' + 
cast(@BaseSize + (8192 * @LoopCount) as nvarchar) + ''MB , MAXSIZE = UNLIMITED,
 FILEGROWTH = 8192MB);'';
            EXEC (@sql);
        END
    END
    
IF @Mirroring = 1 /* RESUME MIRRORING */
    ALTER DATABASE ' + @DatabaseName + ' SET PARTNER RESUME;'
EXEC (@ExecString)

Tags: no tags

Comments

One Response to “HowTo: Grow a very large MS-SQL Log File”

  1. Rudi Maier says:
    February 15, 2012 at 7:08 am

    Trackback…

    [...] Like you can see at this website [...]…

    Reply

Leave a Reply


× nine = twenty seven