Transaction Logs

[SeTi Ni]

Im having issues truncating my transaction logs. I have logs in excess of 40 gigs. All the info in the BOL is very vague. Any assistance would be apreciated.

Regards

SeTi Ni

[Franco]

This stored procedure by Andrew Zanevsky will do the job in SQL Server 7.0

***
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
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
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on

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 #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

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, 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 #loginfo

select @unused1 = @unused -- save for later

select &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = 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( &#39;insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log&#39

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( &#39;dbcc shrinkfile( &#39; + @fileid + &#39; )&#39; )
end

exec( &#39;backup log [&#39; + @db + &#39;] &#39; + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select &#39;After &#39; + convert( varchar, @iteration ) +
&#39; iterations the unused portion of the log has grown from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB.&#39;
union all
select &#39;Since the remaining unused portion is larger than 10 MB,&#39; where @unused > 10
union all
select &#39;you may try running this procedure again with a higher number of iterations.&#39; where @unused > 10
union all
select &#39;Sometimes the log would not shrink to a size smaller than several Megabytes.&#39; where @unused <= 10

else
select &#39;It took &#39; + convert( varchar, @iteration ) +
&#39; iterations to shrink the unused portion of the log from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB&#39;

exec( &#39;drop table table_to_force_shrink_log&#39; )
go
***
Regards
Franco

[TimH]

I use this SP too and it works perfectly !!

TimH


------------
Franco at 8/13/01 10:48:36 AM

This stored procedure by Andrew Zanevsky will do the job in SQL Server 7.0

***
use master
go
if object_id( &#39;sp_force_shrink_log&#39; ) is not null drop proc sp_force_shrink_log
go
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
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = &#39;with truncate_only&#39;
as
set nocount on

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 #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

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( &#39;table_to_force_shrink_log&#39; ) is null
exec( &#39;create table table_to_force_shrink_log ( x nchar(3000) not null )&#39; )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = 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( &#39;insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log&#39

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( &#39;dbcc shrinkfile( &#39; + @fileid + &#39; )&#39; )
end

exec( &#39;backup log [&#39; + @db + &#39;] &#39; + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select &#39;After &#39; + convert( varchar, @iteration ) +
&#39; iterations the unused portion of the log has grown from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB.&#39;
union all
select &#39;Since the remaining unused portion is larger than 10 MB,&#39; where @unused > 10
union all
select &#39;you may try running this procedure again with a higher number of iterations.&#39; where @unused > 10
union all
select &#39;Sometimes the log would not shrink to a size smaller than several Megabytes.&#39; where @unused <= 10

else
select &#39;It took &#39; + convert( varchar, @iteration ) +
&#39; iterations to shrink the unused portion of the log from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB&#39;

exec( &#39;drop table table_to_force_shrink_log&#39; )
go
***
Regards
Franco

[SeTi_Ni]

How long is that sp suposed to run??? it seemed to be in a loop for me

Regards

SeTi_Ni


------------
TimH at 8/13/01 12:37:58 PM

I use this SP too and it works perfectly !!

TimH


------------
Franco at 8/13/01 10:48:36 AM

This stored procedure by Andrew Zanevsky will do the job in SQL Server 7.0

***
use master
go
if object_id( &#39;sp_force_shrink_log&#39; ) is not null drop proc sp_force_shrink_log
go
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
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = &#39;with truncate_only&#39;
as
set nocount on

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 #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

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( &#39;table_to_force_shrink_log&#39; ) is null
exec( &#39;create table table_to_force_shrink_log ( x nchar(3000) not null )&#39; )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = 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( &#39;insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log&#39

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( &#39;dbcc shrinkfile( &#39; + @fileid + &#39; )&#39; )
end

exec( &#39;backup log [&#39; + @db + &#39;] &#39; + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select &#39;After &#39; + convert( varchar, @iteration ) +
&#39; iterations the unused portion of the log has grown from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB.&#39;
union all
select &#39;Since the remaining unused portion is larger than 10 MB,&#39; where @unused > 10
union all
select &#39;you may try running this procedure again with a higher number of iterations.&#39; where @unused > 10
union all
select &#39;Sometimes the log would not shrink to a size smaller than several Megabytes.&#39; where @unused <= 10

else
select &#39;It took &#39; + convert( varchar, @iteration ) +
&#39; iterations to shrink the unused portion of the log from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB&#39;

exec( &#39;drop table table_to_force_shrink_log&#39; )
go
***
Regards
Franco

[Anu]

Hi,

If you really need to get rid of the log the database log. Take the backup.
Sp_detach the database.
You will still have the mdf and ldf file
rename the ldf file and then attch the database with the mdf file only.
The ldf file will automatically be created by the sqlserver with 1 MB.

Suggestion. Do this only at Off hours like Saturday as it will take more time.

-Anu




------------
SeTi_Ni at 8/13/01 3:15:43 PM

How long is that sp suposed to run??? it seemed to be in a loop for me

Regards

SeTi_Ni


------------
TimH at 8/13/01 12:37:58 PM

I use this SP too and it works perfectly !!

TimH


------------
Franco at 8/13/01 10:48:36 AM

This stored procedure by Andrew Zanevsky will do the job in SQL Server 7.0

***
use master
go
if object_id( &#39;sp_force_shrink_log&#39; ) is not null drop proc sp_force_shrink_log
go
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
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = &#39;with truncate_only&#39;
as
set nocount on

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 #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

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( &#39;table_to_force_shrink_log&#39; ) is null
exec( &#39;create table table_to_force_shrink_log ( x nchar(3000) not null )&#39; )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = 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( &#39;insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log&#39

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( &#39;dbcc shrinkfile( &#39; + @fileid + &#39; )&#39; )
end

exec( &#39;backup log [&#39; + @db + &#39;] &#39; + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select &#39;After &#39; + convert( varchar, @iteration ) +
&#39; iterations the unused portion of the log has grown from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB.&#39;
union all
select &#39;Since the remaining unused portion is larger than 10 MB,&#39; where @unused > 10
union all
select &#39;you may try running this procedure again with a higher number of iterations.&#39; where @unused > 10
union all
select &#39;Sometimes the log would not shrink to a size smaller than several Megabytes.&#39; where @unused <= 10

else
select &#39;It took &#39; + convert( varchar, @iteration ) +
&#39; iterations to shrink the unused portion of the log from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB&#39;

exec( &#39;drop table table_to_force_shrink_log&#39; )
go
***
Regards
Franco

[Jon]

MS Knowledge Base has a script to shrink the Transaction Log. Search for &#34;shrink transaction log&#34; and you should be able to modify the script to meet your needs. I&#39;ve used it several times...and it works like a charm.


------------
Anu at 8/13/01 3:40:14 PM

Hi,

If you really need to get rid of the log the database log. Take the backup.
Sp_detach the database.
You will still have the mdf and ldf file
rename the ldf file and then attch the database with the mdf file only.
The ldf file will automatically be created by the sqlserver with 1 MB.

Suggestion. Do this only at Off hours like Saturday as it will take more time.

-Anu




------------
SeTi_Ni at 8/13/01 3:15:43 PM

How long is that sp suposed to run??? it seemed to be in a loop for me

Regards

SeTi_Ni


------------
TimH at 8/13/01 12:37:58 PM

I use this SP too and it works perfectly !!

TimH


------------
Franco at 8/13/01 10:48:36 AM

This stored procedure by Andrew Zanevsky will do the job in SQL Server 7.0

***
use master
go
if object_id( &#39;sp_force_shrink_log&#39; ) is not null drop proc sp_force_shrink_log
go
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
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = &#39;with truncate_only&#39;
as
set nocount on

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 #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)

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( &#39;table_to_force_shrink_log&#39; ) is null
exec( &#39;create table table_to_force_shrink_log ( x nchar(3000) not null )&#39; )

insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = 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( &#39;insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log&#39

select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( &#39;dbcc shrinkfile( &#39; + @fileid + &#39; )&#39; )
end

exec( &#39;backup log [&#39; + @db + &#39;] &#39; + @backup_log_opt )

truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( &#39;dbcc loginfo&#39; )
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 &#39;iteration&#39; = @iteration,
&#39;log size, MB&#39; = @log_size,
&#39;unused log, MB&#39; = @unused,
&#39;shrinkable log, MB&#39; = @shrinkable,
&#39;shrinkable %&#39; = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select &#39;After &#39; + convert( varchar, @iteration ) +
&#39; iterations the unused portion of the log has grown from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB.&#39;
union all
select &#39;Since the remaining unused portion is larger than 10 MB,&#39; where @unused > 10
union all
select &#39;you may try running this procedure again with a higher number of iterations.&#39; where @unused > 10
union all
select &#39;Sometimes the log would not shrink to a size smaller than several Megabytes.&#39; where @unused <= 10

else
select &#39;It took &#39; + convert( varchar, @iteration ) +
&#39; iterations to shrink the unused portion of the log from &#39; +
convert( varchar, @unused1 ) + &#39; MB to &#39; +
convert( varchar, @unused ) + &#39; MB&#39;

exec( &#39;drop table table_to_force_shrink_log&#39; )
go
***
Regards
Franco