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