Thursday, 2 August 2012

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.
 
1:  USE [tempdb]  
2:  GO  
3:  SET ANSI_NULLS ON  
4:  GO  
5:  SET QUOTED_IDENTIFIER ON  
6:  GO  
7:  SET ANSI_PADDING ON  
8:  GO  
9:  if not exists (select * from tempdb.dbo.sysobjects where name = 'temp_logspace')  
10:  CREATE TABLE tempdb.[dbo].[temp_logspace](  
11:   [DatabaseName] [varchar](100) NULL,  
12:   [LogSize] [float] NULL,  
13:   [Logused] [float] NULL,  
14:   [status] [int] NULL  
15:  ) ON [PRIMARY]  
16:  ELSE  
17:  TRUNCATE TABLE tempdb.[dbo].[temp_logspace]  
18:  GO  
19:  SET NOCOUNT ON  
20:  INSERT INTO tempdb.[dbo].temp_logspace   
21:  exec ('DBCC SQLPERF(LOGSPACE) with no_infomsgs')   
22:  SET NOCOUNT OFF  
23:  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  
24:  SELECT @version = convert(varchar,SERVERPROPERTY('productversion'))  
25:  DECLARE dbname CURSOR FOR  
26:  SELECT DatabaseName,LogSize,Logused FROM tempdb.[dbo].temp_logspace --where logused < 50  
27:  OPEN dbname  
28:  FETCH dbname INTO @Databasename,@LogSize,@Logused  
29:  WHILE @@Fetch_Status = 0  
30:  BEGIN  
31:    select @dbv = is_read_only from master.sys.databases where lower(@Databasename) = lower(name)  
32:   IF (EXISTS (select * from master..sysprocesses where dbid = db_id(@Databasename) and status <>'sleeping') OR @dbv = 1)  
33:   PRINT @Databasename+'- ACTIVE TRANSACTION OR READ_ONLY DB'  
34:   ELSE  
35:   BEGIN  
36:   PRINT @Databasename+'- INACTIVE TRANSACTION'  
37:   SET @logusedMB = @LogSize * (@Logused/100)  
38:   --PRINT @logusedMB  
39:    IF @LogSize < 500  
40:    IF @version LIKE '9.%'  
41:    BEGIN  
42:    SET @STR1 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY'  
43:    EXEC(@STR1)  
44:    END   
45:    ELSE  
46:    print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED'  
47:    ELSE  
48:    BEGIN  
49:    IF @version LIKE '9.%'  
50:    BEGIN  
51:    SET @STR2 = 'BACKUP LOG '+@Databasename+' WITH TRUNCATE_ONLY'  
52:    EXEC(@STR2)  
53:    END  
54:    ELSE  
55:    print @Databasename+' - SQL Server 2008 DB TRUNCATE_ONLY IGNORED'  
56:    DECLARE @count float  
57:    DECLARE @endcount float  
58:    SET @count = @LogSize  
59:    SET @endcount = @logusedMB + 300  
60:    WHILE(@count > @endcount AND @endcount <= @LogSize)  
61:    BEGIN  
62:    SET @count = @count - 50  
63:    select @logfilename = name from master.sys.sysaltfiles where dbid = db_id(@Databasename) and fileid = 2  
64:    SET @STR = 'USE '+@Databasename+';'+'DBCC SHRINKFILE('+@logfilename+','+convert(varchar,convert(int,@count))+') with no_infomsgs'  
65:    EXEC(@STR)  
66:    print @STR  
67:    END  
68:    END  
69:   END  
70:  FETCH dbname INTO @Databasename,@LogSize,@Logused       
71:  END  
72:  CLOSE dbname  
73:  DEALLOCATE dbname  
74:  SET ANSI_PADDING OFF  
75:  GO  

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

No comments:

Post a Comment