Howto Shrink All Logfiles On SQL Server

I use this script as a backup when something hasn’t gone to plan and the transaction logs have blown out on SQL Server. It will shrink all of the logfiles on the sql server and free up some space – provided the transaction log backups have been working of course.

-- Shrink every logfile on the database server
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