Shrink All Log Files Within A MS SQL 2008 Instance
This is an update to a previous post (Shrink All Log Files Within A MS SQL Instance) that has been edited to work in SQL Server 2008.
<br /> declare @ssql nvarchar(4000)<br /> SET @ssql= '<br /> if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')<br /> begin<br /> use [?]<br /> declare @tsql nvarchar(4000) set @tsql = ''''<br /> declare @recmodel nvarchar(10)<br /> declare @iLogFile int<br /> declare LogFiles cursor for<br /> select fileid from sysfiles where status & 0x40 = 0x40<br /> open LogFiles<br /> fetch next from LogFiles into @iLogFile<br /> while @@fetch_status = 0<br /> begin<br /> select @recmodel = (SELECT recovery_model_desc FROM sys.databases WHERE name = ''?'' )<br /> if @recmodel = ''FULL''<br /> begin<br /> set @tsql = @tsql + ''use [?] ''<br /> set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY SIMPLE ''<br /> set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br /> set @tsql = @tsql + ''ALTER DATABASE [?] SET RECOVERY FULL ''<br /> end<br /> else<br /> set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br /> fetch next from LogFiles into @iLogFile<br /> end<br /> --set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br /> --print @tsql<br /> exec(@tsql)<br /> close LogFiles<br /> DEALLOCATE LogFiles<br /> end'<br /> exec sp_msforeachdb @ssql<br />
June 18, 2010 | Posted by Stewart Schatz
Categories:
Tags: |