SQL Server Performance Forum – Threads Archive

Help with Transaction Log Shrink

I can not get my transaction log files to shrink, what is the code syntex using the DBCC ShrinkFile to shrink my transaction log……Thank in advance.

Maybe this link can help:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch12.asp

I got this script from SQL Server Professional Magazine in an article by Andrew Zanevsky. It performs transactions to fill up virtual log files and continually shrinks the log until the desired level of shrinkage is achieved. I have sometimes had to run this more than once. But I will only use it if the DBCC SHRINKFILE doesn’t work for some reason.
create proc sp_force_shrink_log
/*
*************************************************************
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage:exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 – 08/18/2000
[email protected] Input Params:
————-
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = ‘with truncate_only’. Backup options. Output Params:
————– Return: Results:
——— Locals:
——–
@errHolds error value Modifications:
————– *************************************************************
*/
@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 ‘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, 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 ‘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
if object_id( ‘sp_force_shrink_log’) Is Null
select ‘sp_force_shrink_log Not Created’
else
select ‘sp_force_shrink_log Created’
go

Refer to the books online for DBCC SHRINKFILE statement to shrink transaction log files, and shrink operation is not immediate it depends on the next transaction log backup execution.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>