Friday, January 29, 2010

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

No comments: