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
