How To: Automated shrink transaction logs in SQL

Making a scheduled automated shrink logs for the databases with specific space to shrink.

DECLARE @Step1 VARCHAR(200)
DECLARE @Name varchar(50)

DECLARE DBNames CURSOR
FOR
SELECT NAME FROM sysdatabases WHERE dbid > 4
OPEN DBNames

FETCH NEXT FROM DBNames INTO @Name WHILE (@@FETCH_STATUS <> -1)

BEGIN

SET @Step1 = 'USE ' + @Name + '' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(10)
SET @Step1 = @Step1 + 'DBCC SHRINKFILE (' +@Name + '_LOG, 500);' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(10)

EXEC (@Step1)

FETCH NEXT FROM DBNames INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames

(54848)

Leave a Reply

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