Monday, December 12, 2011

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

No comments:

Post a Comment