USE [master] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_force_shrink_log]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_force_shrink_log] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[sp_force_shrink_log] ---------------------------------------------------------------------------- ---- -- Purpose: Shrink transaction file log of the current database in Microsoft -- SQL Server 7.0 or 2000. The same code works on both platforms. -- -- Author: Andrew Zanevsky, AZ Databases, Inc., zanevsky@azdatabases.com -- -- Version: Original: 12/25/1999. Current: v9 - 07/09/2002 (2000-compatible) -- v9 fixes a bug in handling databases with multiple log files -- -- License: Freeware. No license fees for use or re-distribution. -- The code can only be re-distributed in its entirety, unaltered, -- including all comments. Any alterations may constitute a violation -- of author's copyrights. -- -- The author and his company make no guarantees and take no -- responsibility for any changes to your system caused by this -- software. Use at your own risk. -- -- From the author: -- I have applied my best knowledge and professional judgement while -- creating this code. I have considered comments from multiple users -- of earlier version of the procedure and made several modifications -- as a result. The procedure has been very popular. Feel free to email -- your comments to zanevsky@azdatabases.com. I don't promise to reply -- to everyone, but I will make a reasonable effort to do so, -- especially if you encounter a problem with my procedure. -- I have seen a post by Kalen Delaney that SQL Server 2000 handles -- log truncation much better than 7.0 and it is no longer necessary to -- move the active portion of the log in order to shrink it. But I have -- received a report from one user who was unable to shrink the log in -- SQL Server 2000 using BACKUP LOG followed by DBCC SHRINKFILE, so I -- made my procedure compatible with SQL Server 2000. Enjoy! ---------------------------------------------------------------------------- ---- @target_percent tinyint = 0, @target_size_MB decimal(15,2) = 10, @max_iterations int = 1000, @backup_log_opt nvarchar(1000) = 'with truncate_only' as set nocount on set rowcount 0 declare @ver smallint set @ver = case when @@version like 'Microsoft SQL Server 7.00%' then 7 else 8 end declare @db sysname, @last_row int, @log_size decimal(15,2), @unused1 decimal(15,2), @unused decimal(15,2), @shrinkable decimal(15,2), @iteration int, @file_max int, @file int, @fileid varchar(5) select @db = db_name(), @iteration = 0 create table #loginfo7 ( id int identity, FileId int, FileSize numeric(22,0), StartOffset numeric(22,0), FSeqNo int, Status int, Parity smallint, CreateTime datetime ) create table #loginfo8 ( id int identity, FileId int, FileSize numeric(22,0), StartOffset numeric(22,0), FSeqNo int, Status int, Parity smallint, CreateLSN varbinary(48) ) create unique clustered index loginfo_FSeqNo on #loginfo7 ( FileId, FSeqNo, StartOffset ) create unique clustered index loginfo_FSeqNo on #loginfo8 ( FileId, FSeqNo, StartOffset ) create table #logfiles ( id int identity(1,1), fileid varchar(5) not null ) exec( ' insert #logfiles ( fileid ) select convert( varchar, fileid ) from [' + @db + ']..sysfiles where status & 0x40 = 0x40 ') select @file_max = count(*) from #logfiles if object_id( 'table_to_force_shrink_log' ) is null exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' ) if @ver = 7 begin insert #loginfo7 ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' ) select @last_row = @@rowcount select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00 from #loginfo7 end else begin insert #loginfo8 ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' ) select @last_row = @@rowcount select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00 from #loginfo8 end select @unused1 = @unused -- save for later select 'iteration' = @iteration, 'log size, MB' = @log_size, 'unused log, MB' = @unused, 'shrinkable log, MB' = @shrinkable, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size ) while @shrinkable * 100 / @log_size > @target_percent and @shrinkable > @target_size_MB and @iteration < @max_iterations begin select @iteration = @iteration + 1 -- this is just a precaution exec( 'insert table_to_force_shrink_log select name from sysobjects delete table_to_force_shrink_log') select @file = 0 while @file < @file_max begin select @file = @file + 1 select @fileid = fileid from #logfiles where id = @file exec( 'dbcc shrinkfile( ' + @fileid + ' )' ) end exec( 'backup log [' + @db + '] ' + @backup_log_opt ) if @ver = 7 begin truncate table #loginfo7 insert #loginfo7 ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' ) select @last_row = @@rowcount select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00 from #loginfo7 end else begin truncate table #loginfo8 insert #loginfo8 ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' ) select @last_row = @@rowcount select @log_size = sum( FileSize ) / 1048576.00, @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00, @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00 from #loginfo8 end select 'iteration' = @iteration, 'log size, MB' = @log_size, 'unused log, MB' = @unused, 'shrinkable log, MB' = @shrinkable, 'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size ) end if @unused1 < @unused select 'After ' + convert( varchar, @iteration ) + ' iterations the unused portion of the log has grown from ' + convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB.' union all select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10 union all select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10 union all select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10 else select 'It took ' + convert( varchar, @iteration ) + ' iterations to shrink the unused portion of the log from ' + convert( varchar, @unused1 ) + ' MB to ' + convert( varchar, @unused ) + ' MB' exec( 'drop table table_to_force_shrink_log' ) GO