Shrink de Logs
De MSTECH wiki
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