Blog Post

Auto Shrink of all T-Log File on a SQL Instance - TSQL

,


This Script will shrink all the T-Log files of SQL instance if there is no active tracsaction on the database. The Script will automatically ignore the DB if there are some active transactions.
Scripted for SIMPLE RECOVERY databases, comment BACKUP LOG line for FULL RECOVERY databases. The Log file will be shrunk till the free space is only 300 MB.

USE [tempdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIERON

GO

SET ANSI_PADDING ON

GO

if not exists (select * from tempdb.dbo.sysobjects where name = 'temp_logspace')

CREATE TABLE tempdb.[dbo].[temp_logspace](

 [DatabaseName][varchar](100) NULL,

 [LogSize] [float] NULL,

 [Logused] [float] NULL,

 [status] [int] NULL

) ON [PRIMARY]

ELSE

TRUNCATE TABLE tempdb.[dbo].[temp_logspace]

GO

SET NOCOUNT ON

INSERT INTO tempdb.[dbo].temp_logspace

exec ('DBCC SQLPERF(LOGSPACE) with no_infomsgs')

SET NOCOUNT OFF

DECLARE @Databasename varchar(100),@LogSize float,@Logused float,@logusedMB float,@logfilename varchar(200),@STR varchar(5000),@version varchar(15),@STR1 varchar(5000),@STR2 varchar(5000),@dbv int

SELECT @version = convert(varchar,SERVERPROPERTY('productversion'))

DECLARE dbname CURSOR FOR

SELECT DatabaseName,LogSize,Logused FROM tempdb.[dbo].temp_logspace --where logused < 50

OPEN dbname

FETCH dbname INTO @Databasename,@LogSize,@Logused

WHILE @@Fetch_Status= 0

BEGIN

    select @dbv = is_read_only from master.sys.databases where lower(@Databasename) = lower(name)

 IF (EXISTS (select * from master..sysprocesses where dbid = db_id(@Databasename) and status <>'sleeping') OR @dbv = 1)

 PRINT @Databasename+'- ACTIVE TRANSACTION OR READ_ONLY DB'

 ELSE

  BEGIN

  PRINT @Databasename+'- INACTIVE TRANSACTION'

  SET @logusedMB = @LogSize * (@Logused/100)

  --PRINT @logusedMB

   IF @LogSize < 500

    IF @version LIKE '9.%'

    BEGIN

    SET @STR1 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY'

    EXEC(@STR1)

    END

    ELSE

    print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED'

   ELSE

   BEGIN

    IF @version LIKE '9.%'

    BEGIN

    SET @STR2 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY'

    EXEC(@STR2)

    END

    ELSE

    print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED'

   DECLARE @count float

   DECLARE @endcount float

   SET @count = @LogSize

   SET @endcount = @logusedMB + 300

    WHILE(@count > @endcount AND @endcount <= @LogSize)

    BEGIN

    SET @count = @count - 50

    select @logfilename = name from master.sys.sysaltfiles where dbid = db_id(@Databasename) and fileid = 2

    SET @STR = 'USE '+@Databasename+';'+'DBCC SHRINKFILE('+@logfilename+','+convert(varchar,convert(int,@count))+') with no_infomsgs'

    EXEC(@STR)

    print @STR

    END

   END

  END

FETCH dbname INTO @Databasename,@LogSize,@Logused         

END

CLOSE dbname

DEALLOCATE dbname

SET ANSI_PADDING OFF

GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating