Shrink Log File to Target Percentage

if object_id('dbo.usp_maintain_database_log') is not null
  drop procedure dbo.usp_maintain_database_log
go

create procedure dbo.usp_maintain_database_log  
 @target_free_pct int = 10,  
 @minimum_size_mb int = 100,  
 @backup_log_file_path nvarchar(max) = 'T:\SQLDUMP\LOGDUMP\'  
  as  
    
/*   
  
Name:   dbo.usp_maintain_database_log  
Author:        Tommy Bollhofer (tbollhofer2@gmail.com)  
Purpose:       Performs a checkpoint or a transaction log backup followed by a DBCC SHRINKFILE   
   on the transaction log to the target percentage specified relative to the total data file(s) size.    
Usage:   exec dbo.usp_maintain_database_log  
   @target_free_pct = 10,  
   @minimum_size_mb = 100,  
   @backup_log_file_path = 'T:\SQLDUMP\LOGDUMP\'      
Last Modified: 09/10/2009  
*/  
  
set nocount on  
  
declare @free_pct int,   
     @file_name sysname,  
     @total_log_size_mb int,  
     @target_size_mb int,  
     @database_name sysname,  
     @backup_file_name nvarchar(max),  
     @sql nvarchar(max),  
     @cmd varchar(1000)  
  
create table #results   
(  
 [database_name] sysname,   
 [file_name] sysname,  
 [file_type] varchar(4),   
 [total_size_mb] int,   
 [available_space_mb] int,   
 [free_space_pct] int  
);  
  
with file_sizes_cte as  
(   
 select db_name() as database_name,   
     [name] as [file_name],     
     [file_type] =    
     case type   
     when 0 then 'data'when 1 then 'log'  
     end,   
     [total_size_mb] =   
     case ceiling([size]/128)    
     when 0 then 1   
     else ceiling([size]/128)   
     end,   
     [available_space_mb] =    
     case ceiling([size]/128)   
     when 0 then (1 - cast(fileproperty([name], 'spaceused') as int) /128)   
     else (([size]/128) - cast(fileproperty([name], 'spaceused') as int) /128)   
     end  
      from sys.database_files with(nolock)   
)  
  
insert into #results   
(  
 [database_name],   
 [file_name],  
 [file_type],   
 [total_size_mb],   
 [available_space_mb],  
 [free_space_pct]  
)  
  
 select [database_name],   
  [file_name],  
  [file_type],   
  [total_size_mb],   
  [available_space_mb],  
  ceiling(cast([available_space_mb] as decimal(10,1)) / [total_size_mb]*100) as [free_space_pct]  
   from file_sizes_cte  
  
 select @database_name = database_name,  
  @file_name = [file_name],  
  @total_log_size_mb = total_size_mb,  
  @free_pct = available_space_mb * 100 / total_size_mb  
   from #results  
  where file_type = 'log'  
  
 select @target_size_mb = convert(int,sum(total_size_mb) * .10)  
   from #results where file_type = 'data' 

/*
--debug
print @free_pct 
print  '>='
print @target_free_pct 
print 'and' 
print @total_log_size_mb 
print '>=' 
print @minimum_size_mb 
print 'and' 
print @target_size_mb 
print '<' 
print @total_log_size_mb
*/
  
if (@free_pct >= @target_free_pct and @total_log_size_mb >= @minimum_size_mb) and (@target_size_mb < @total_log_size_mb)
begin  
      
 if (select cast(databasepropertyex(db_name(), 'recovery') as varchar(10))) = 'SIMPLE'  
 begin  
   set @sql = 'checkpoint'  
  exec sp_executesql @sql      
 end  
  
 if (select cast(databasepropertyex(db_name(), 'recovery') as varchar(10))) = 'FULL'  
 begin  
     set @backup_file_name = upper(db_name()) + '_Log_' + replace(replace(replace(convert(varchar(50), getdate(), 120), '-', ''), ' ', ''), ':', '') + '.lbk'  
     set @sql = 'backup log ' + @database_name + ' to disk = ''' + @backup_log_file_path + @backup_file_name + ''' '  
    exec sp_executesql @sql    
 end  
  
exec( 'dbcc shrinkfile( ' + @file_name + ',' + @target_size_mb +' )' )  
 
end  
  
drop table #results

go