[SQL Server] Shrinker tous le fichiers log de toutes les BD
declare @logname nvarchar(128)
declare @dbname nvarchar(128)
declare @dynamic_command nvarchar(1024)
set @dynamic_command = null
declare log_cursor cursor for
select db_name(mf.database_id),name
from sys.master_files mf
where mf.database_id not in(1,2,3,4) --avoid system databases
and mf.name not like 'ReportServer$%'
and right(mf.physical_name,4) = '.ldf' and mf.state_desc='online'
open log_cursor
fetch next from log_cursor into @dbname,@logname
while @@fetch_status = 0
begin
set @dynamic_command = 'USE '+@dbname+' DBCC SHRINKFILE(N'''+@logname+''',0,TRUNCATEONLY)'
exec sp_executesql@dynamic_command
fetch next from log_cursor into @dbname,@logname
set @dynamic_command = null
end
close log_cursor
deallocate log_cursor