Code:
--CREATE proc sp_force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@azdatabases.com
--------------------------------------------------------------------------------
-- These were the input parameters
Declare @target_percent tinyint
, @target_size_MB int
, @max_iterations int
, @backup_log_opt nvarchar(1000)
--as
set nocount on
--initialize the input parameters
Set @target_percent = 0
Set @target_size_MB = 2
Set @max_iterations = 1000
Set @backup_log_opt = 'with truncate_only'
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
--drop table #loginfo
--drop table #logfiles
create table #loginfo (
[id] int identity
, FileId int
, FileSize numeric(22,0)
, StartOffset numeric(22,0)
, FSeqNo int
, Status int
, Parity smallint
, CreateLSN float
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( [id] int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid )
select convert( varchar, fileid )
from sysfiles
where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( 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 #loginfo
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 )
truncate table #loginfo
insert #loginfo ( 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 #loginfo
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
Reply With Quote
That is crazy..... but it works right? Hmmmm..... I think we have a couple of clients for which this could be handy.
Originally Posted by szlamany
I'd rate, but I musta have done that for you recently as it won't let me at the moment - ggrrr!)
™
