Shrink de Logs

Este script realiza o shrink de logs de todas as bases de uma dada instância.

 1 declare @tab_controle table (Name nvarchar(256), controle tinyint, LogFile nvarchar(256))
 2 insert into @tab_controle
 3 select db.name, 0 as controle, fl.name as logfile
 4   from sys.databases db inner join sys.master_files fl
 5        on db.database_id = fl.database_id
 6 where db.database_id > 4
 7    and db.is_read_only = 0
 8    and db.state = 0
 9    and recovery_model = 1
10    and fl.type = 1
11 declare @DATABASE nvarchar(256), @LOGFILE nvarchar(256)
12 while exists (select name from @tab_controle where controle = 0)
13 begin
14      select @DATABASE = name, @LOGFILE = LogFile from @tab_controle where controle = 0
15      EXEC ('USE [' + @DATABASE + ']; ALTER DATABASE [' + @DATABASE + '] SET RECOVERY SIMPLE;')
16      EXEC ('USE [' + @DATABASE + ']; DBCC SHRINKFILE ([' + @LOGFILE + '], 0);')
17      EXEC ('USE [' + @DATABASE + ']; ALTER DATABASE [' + @DATABASE + '] SET RECOVERY FULL;')
18      update @tab_controle set controle = 1 where Name = @DATABASE
19 end
20 use [tempdb]
21 GO
22 DBCC FREEPROCCACHE
23 GO
24 DBCC DROPCLEANBUFFERS
25 go
26 DBCC FREESYSTEMCACHE ('ALL')
27 GO
28 DBCC FREESESSIONCACHE
29 GO
30 DBCC SHRINKFILE (TEMPDEV,1024)
31 GO