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);
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;

SET @BaseSize = 4000; 
SET @TargetSize = 40000; 
SET @LoopCount = 0; 
SET @NumLoops = (@TargetSize - @BaseSize) / 8192 

USE ' + @DatabaseName + ';

SET @LogFileName = 
    (SELECT TOP (1) [name] 
    FROM sys.database_files 
    WHERE type_desc = ''LOG'' 
    ORDER BY file_id DESC);

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;
    SET @Mirroring = 0;

IF @Mirroring = 1 /* PAUSE MIRRORING */

USE ' + @DatabaseName + ';

DBCC SHRINKFILE (@LogFileName , @BaseSize); 

 TO @BaseSize 
IF (
    SELECT ([size] * 8)/1024 
    FROM sys.database_files 
    WHERE type_desc = ''LOG'' AND name = @LogFileName) = @BaseSize 
        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)


Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the anti-spam question: * Time limit is exhausted. Please reload CAPTCHA.