Shrink de Logs

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

declare @tab_controle table (Name nvarchar(256), controle tinyint, LogFile nvarchar(256))
insert into @tab_controle
select db.name, 0 as controle, fl.name as logfile
  from sys.databases db inner join sys.master_files fl
       on db.database_id = fl.database_id
where db.database_id > 4
   and db.is_read_only = 0
   and db.state = 0
   and recovery_model = 1
   and fl.type = 1
declare @DATABASE nvarchar(256), @LOGFILE nvarchar(256)
while exists (select name from @tab_controle where controle = 0)
begin
     select @DATABASE = name, @LOGFILE = LogFile from @tab_controle where controle = 0
     EXEC ('USE [' + @DATABASE + ']; ALTER DATABASE [' + @DATABASE + '] SET RECOVERY SIMPLE;')
     EXEC ('USE [' + @DATABASE + ']; DBCC SHRINKFILE ([' + @LOGFILE + '], 0);')
     EXEC ('USE [' + @DATABASE + ']; ALTER DATABASE [' + @DATABASE + '] SET RECOVERY FULL;')
     update @tab_controle set controle = 1 where Name = @DATABASE
end
use [tempdb]
GO
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024)
GO