SQL Server: Automated log file maintenance

Ok you've all seen it before. You remote desktop onto a client's sql server to upload your app, when your upload fails due to a lack of disk space. Darnit! So you have to reduce the LDF's, which has grown to be several GBs large.

The following little script will reduce all the non-system db's log files. One can also schedule this script as a job that automatically reduces the log files. (Disclaimer: Read-up on the function log files fulfil before simply using this script. I would not recommend running this on critical production systems.)

-- This gets us all on the same page
use [master]

-- Define some variables
declare @statement varchar(2000) 
declare @dbname sysname
declare dbname_cursor cursor for

-- Select all non-system db's
select [name] from master.dbo.sysdatabases where 
  [name] <> 'master' and 
  [name] <> 'model' and 
  [name] <> 'msdb' and 
  [name] <> 'tempdb'

-- Iterate through each db record
open dbname_cursor
fetch next from dbname_cursor into @dbname
while @@fetch_status <> -1
  begin
    select @statement = ''
    select @statement = @statement + 'use ' + @dbname + '; '

    -- Backup db by runcating only (will not reduce physical file size)
    select @statement = @statement + 'backup log ' + @dbname + ' with truncate_only; '

    -- Get the db log file name
    select @statement = @statement + 'declare @log_file varchar(2000); '
    select @statement = @statement + 'select @log_file = [name] from sys.database_files where type_desc = ''LOG''; '

    -- Reduce the physical file size
    select @statement = @statement + 'exec(''dbcc shrinkfile ('' + @log_file + '', 0)''); '    
    exec(@statement)
    fetch next from dbname_cursor into @dbname
  end
close dbname_cursor
deallocate dbname_cursor