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

  1. SQL server works a lot in the software industry as best assignment service. People love to us ethics for the data shrinking purpose. Is server helps to increase the capacity of space in the system. This lead for the better performance of the system. All log files can be shrinking by such system.

Leave a Reply

Your email address will not be published. Required fields are marked *