March 4, 2003 at 12:57 am
Hello
I want to clear the transaction protocol without taking a backup (transaction log 19GB). I have tried the following command
Backup Log Db_name with Truncate_Only
But after executing this command the logdatei is still 19GB.
Why this command not clear the log data
B. Regards
Joseph
March 4, 2003 at 3:47 am
The problem here is Backupo Log just marks the transactions for removal.
Depending on the SQL Version you are running you need to shirnk the file too.
For SQL 2000
DBCC SHRINKFILE the logfile should do the trick.
For SQL 7 though the virtual logs within the TL file do not always break up properly. To get top shrink see the follwoing script which does very nicely.
http://qa.sqlservercentral.com/scripts/contributions/26.asp
Do not try the script on 2000 as should not work at all but 2000 always shrinks nicely. If you do have troubles with 2000 use EM to srinkg the DB Files and try backup log again. On a few occassions I have had to run a couple of times.
March 7, 2003 at 8:17 am
Antares, the same sp by Andrew Zanevsky is updated also for SQL 2K and here it is:
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 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: v8 - 12/17/2001 (2000-compatible)
-- v8 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 ( FSeqNo, StartOffset )
create unique clustered index loginfo_FSeqNo on #loginfo8 ( 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
Franco
Franco
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply