Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

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

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...