TSQL script to shrink all log files

I got alerted over the weekend to a SQL server running out of disk space on one of its drives. Upon further inspection, several of the LOG files had grown and were filling the log drive. I went to shrink the files, but realized there must be a better way.

A little googling and I found http://codesnippets.joyent.com/posts/show/665. This script cycles through all DBs and shrinks the LOG files for each DB. Simply schedule it with the SQL Server Agent and your good to go.

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 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))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        close LogFiles
        DEALLOCATE LogFiles

exec sp_msforeachdb @ssql

5 thoughts on “TSQL script to shrink all log files

Leave a Reply