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.
if not exists (select * from tempdb.dbo.sysobjects where name = 'temp_logspace') CREATE TABLE tempdb.[dbo].[temp_logspace]( [DatabaseName][varchar](100) NULL, TRUNCATE TABLE tempdb.[dbo].[temp_logspace] INSERT INTO tempdb.[dbo].temp_logspace exec ('DBCC SQLPERF(LOGSPACE) with no_infomsgs') 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 FETCH dbname INTO @Databasename,@LogSize,@Logused 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' PRINT @Databasename+'- INACTIVE TRANSACTION' SET @logusedMB = @LogSize * (@Logused/100) SET @STR1 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY' print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED' SET @STR2 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY' print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED' SET @endcount = @logusedMB + 300 WHILE(@count > @endcount AND @endcount <= @LogSize) 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' FETCH dbname INTO @Databasename,@LogSize,@Logused GO