SQL script to truncate and shrink log file of all database
This is a quick method to truncate and shrink the log file of all database. I usually use for clean up development database.
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 @sLogFileName varchar(55) 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))+'', 500) '' fetch next from LogFiles into @iLogFile end set @tsql = ''USE [?]; '' + @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql --print @tsql --for debugging exec(@tsql) close LogFiles DEALLOCATE LogFiles end' exec sp_msforeachdb @ssql
Comments