clear transaction log

  • 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

  • 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://www.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.

  • 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 )

    selectconvert( 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