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
set @tsql = @tsql + '
'DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 500) ''
fetch next from LogFiles into @iLogFile
--set @tsql = '
'USE [?]; '' + @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
--print @tsql --for debugging
close LogFiles

exec sp_msforeachdb @ssql


Popular posts from this blog

Monitoring Raspberry Pi Performance Using Cacti

Using NAGIOS to Check the Physical Memory Available on a Windows Host

Error 2738 Occurs When Installing An MSI Package