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_IDENTIFIER ON
	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

view raw SQLTechnet - AutoShrinkT-Log.sql hosted with ❤ by GitHub

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.