Shrink Log Files of all databases
Snippet Menu
5
Mon. Oct. 9th, 2006 2:23 AM
Shrink databases' log files to minimal size. Runs on every database on server, using undocumented 
procedure.
procedure. declare @ssql nvarchar(4000)
SET @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
fetch next from LogFiles into @iLogFile
end
set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
--print @tsql
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'
exec sp_msforeachdb @ssql
Sergey Okhotny
Comments
Voting
Votes Up
Votes Down
There are currently no comments for this snippet.