Monday, September 20, 2010

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
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'

exec sp_msforeachdb @ssql

5 comments:

oskbo said...

Do never run BACKUP LOG [?] WITH TRUNCATE_ONLY unless it is a test/play database. You lose the ability to restore the database to a point in time.

Here is a article why: http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/

Dan Thompson said...

Be careful, as the sysfiles compatibility view is going to be removed from future versions of SQL:

http://msdn.microsoft.com/en-us/library/ms178009.aspx

Dan Thompson said...

...sorry should have said as well that TRUNCATE_ONLY is no longer available in SQL 2008 R2

max den said...

Amazing post

Noorun Khatiha said...

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.