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 &amp; 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 />
linkedin Shrink All Log Files Within A MS SQL 2008 Instancedigg Shrink All Log Files Within A MS SQL 2008 Instanceshare save 120 16 Shrink All Log Files Within A MS SQL 2008 Instance

Leave a Reply