Shrink Transaction Log

At a company I worked, they were in the process of setting up ERP software that was bought by their  management.

Regardless the quality (or lack thereof) of the software that was installed for use at the company, one of my duties was to care for the good maintenance of the SQL Servers.

Well, long story short, this ERP software that was installed at the company I worked for created a log of 30 GB for a database of 1 GB.

It was outrageous.

So in order to prevent it to grow further I created a job that shrink the log daily.

Although it is pretty straightforward to do this for a single database, I decided to raise the bar a bit and create the same procedure for every single database on all our servers.

How to do this? It’s a two steps procedure.

First I ran a query that creates the SQL commands to generate the procedures on every database on the server.

Then I create a job that will run on the server daily, just prior to the full database backup, to keep the log file small.

USE master
GO

SET NOCOUNT ON

--
-- Creates a cursor with all databases in the server
--
DECLARE curdb CURSOR FOR
SELECT dbid, name
  FROM sysdatabases
 WHERE DatabaseProperty(name, 'IsOffline') = 0

DECLARE @dbid    int
       ,@dbname  varchar(256)
       ,@name    varchar(256)
       ,@union   bit
       ,@strSQL  varchar(8000)
       ,@pos     int

SET @strSQL = ''
SET @union = 0

--
-- Get the name of log files for all databases
--
OPEN curdb
FETCH curdb into @dbid, @dbname

WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF LEN(@strSQL) > 7000 
    BEGIN
        SET @pos = Len(@strSQL) - CharIndex(char(10), reverse(@strSQL)) + 1
        PRINT SubString(@strSQL, 1, @pos)
        SET @strSQL = SubString(@strSQL, @pos, 1000)
    END

    SET @strSQL = @strSQL + char(13) + char(10) + 'USE ' + @dbname + char(13) + char(10)
    SET @strSQL = @strSQL + 'GO' + char(13) + char(10)
    SET @strSQL = @strSQL + char(13) + char(10)

    SET @strSQL = @strSQL + 'IF EXISTS (SELECT * FROM sysobjects WHERE name = ''sp_shrinklogfile'' AND type = ''P'')' + char(13) + char(10)
    SET @strSQL = @strSQL + '    DROP PROCEDURE dbo.sp_shrinklogfile' + char(13) + char(10)
    SET @strSQL = @strSQL + 'GO' + char(13) + char(10)
    SET @strSQL = @strSQL + char(13) + char(10)

    SET @strSQL = @strSQL + Char(13) + Char(10) + 'CREATE PROCEDURE dbo.sp_shrinklogfile'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    @NewSize int'
    SET @strSQL = @strSQL + Char(13) + Char(10) + 'AS'
    SET @strSQL = @strSQL + Char(13) + Char(10) + 'BEGIN'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET NOCOUNT ON'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    DECLARE @MaxMinutes INT, @Factor FLOAT'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET @MaxMinutes = 1'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET @Factor     = 0.95'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    DECLARE @LogicalFileName varchar(250)'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@MaxSize      BIGINT'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@OriginalSize BIGINT'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@StringData   VARCHAR(500)'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SELECT TOP 1'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '            @LogicalFileName = RTRIM( [name] ),'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '            @MaxSize = CASE [maxsize]'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '                        WHEN -1 THEN -1'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '                        ELSE ((cast ([maxsize] as bigint) * 8))/1024'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '                       END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '      FROM sysfiles'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '     WHERE [groupid] = 0'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '     ORDER '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        BY [size] DESC'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    IF @MaxSize > 0 AND @NewSize > @MaxSize'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    BEGIN'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        SET @NewSize = @MaxSize'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    IF ( OBJECT_ID(''[dbo].[DummyTrans]'') IS NOT NULL )'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    BEGIN'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        DROP TABLE [' + @dbname + '].dbo.[DummyTrans]'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    CREATE TABLE [' + @dbname + '].dbo.[DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL )'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    DECLARE @Counter   INT'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@MaxCount  INT'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@StartTime DATETIME'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '           ,@TruncLog  VARCHAR(500)'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    DBCC SHRINKFILE ( @LogicalFileName, @NewSize)'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET @TruncLog = ''BACKUP LOG ['' + db_name() + ''] WITH TRUNCATE_ONLY'''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    EXEC (@TruncLog)'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    IF @OriginalSize / @Factor > 50000'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        SET @MaxCount = 50000'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    ELSE'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        SET @MaxCount = @OriginalSize * @Factor'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET @Counter = 0'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET @StartTime = GETDATE()'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    WHILE ((@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '      AND  (@OriginalSize = (SELECT size FROM sysfiles WHERE name =@LogicalFileName)) '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '      AND  ((@OriginalSize * 8 / 1024) > @NewSize))'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    BEGIN'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        WHILE (( @Counter < @MaxCount) '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '          AND  ( @MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '          AND  ( @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) '
    SET @strSQL = @strSQL + Char(13) + Char(10) + '          AND  (( @OriginalSize * 8 / 1024) > @NewSize))'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        BEGIN '
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '            INSERT INTO [' + @dbname + '].dbo.DummyTrans VALUES (''Fill Log'')'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '            DELETE FROM [' + @dbname + '].dbo.DummyTrans'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '            SET @Counter = @Counter + 1'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '        EXEC( @TruncLog )'
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    DROP TABLE [' + @dbname + '].dbo.DummyTrans'
    SET @strSQL = @strSQL + Char(13) + Char(10) + ''
    SET @strSQL = @strSQL + Char(13) + Char(10) + '    SET NOCOUNT OFF'
    SET @strSQL = @strSQL + Char(13) + Char(10) + 'END'
    SET @strSQL = @strSQL + Char(13) + Char(10) + 'GO'

    FETCH curdb into @dbid, @dbname
END

--
-- Reopen the cursor
-- 
CLOSE curdb
OPEN curdb

--
-- Create the procedure at the master db
--

SET @strSQL = @strSQL + char(13) + char(10) + 'USE master' + char(13) + char(10)
SET @strSQL = @strSQL + 'GO' + char(13) + char(10)
SET @strSQL = @strSQL + char(13) + char(10)

SET @strSQL = @strSQL + 'IF EXISTS (SELECT * FROM sysobjects WHERE name = ''sp_shrink_all_logs'' AND type = ''P'')' + char(13) + char(10)
SET @strSQL = @strSQL + '    DROP PROCEDURE dbo.sp_shrink_all_logs' + char(13) + char(10)
SET @strSQL = @strSQL + 'GO' + char(13) + char(10)
SET @strSQL = @strSQL + char(13) + char(10)

SET @strSQL = @strSQL + 'CREATE PROCEDURE dbo.sp_shrink_all_logs' + char(13) + char(10)
SET @strSQL = @strSQL + '    @NewSize int = 20' + char(13) + char(10)
SET @strSQL = @strSQL + 'AS' + char(13) + char(10)
SET @strSQL = @strSQL + 'BEGIN' + char(13) + char(10)

FETCH curdb into @dbid, @dbname

WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF LEN(@strSQL) > 7000 
    BEGIN
        SET @pos = Len(@strSQL) - CharIndex(char(10), reverse(@strSQL)) + 1
        PRINT SubString(@strSQL, 1, @pos)
        SET @strSQL = SubString(@strSQL, @pos, 1000)
    END

    SET @strSQL = @strSQL + '    EXEC ' + left(@dbname + '..sp_shrinklogfile' + space(40), 40) + ' @NewSize' + char(13) + char(10)
    FETCH curdb into @dbid, @dbname
END

SET @strSQL = @strSQL + 'END' + char(13) + char(10)
SET @strSQL = @strSQL + 'GO' + char(13) + char(10)
SET @strSQL = @strSQL + char(13) + char(10)

CLOSE curdb
DEALLOCATE curdb

PRINT @strSQL

SET NOCOUNT OFF