Transaction log not clearing out

Question

Transaction log not clearing out

TomCarroll

SSC Rookie

Points: 35
More actions
December 16, 2002 at 4:46 pm

#64091

Hi all -
I am finding that in one situation SQL Server 2000 behaves in a very counterintuitive fashion (to me, anyhow).
I have a small lightly-used database set up using the Simple recovery model. Using "dbcc opentran (dbname)" I see that there are no open transactions on the database. Doing a "dbcc sqlperf (logspace)" I see that the T-log is about 20% full. I do a "backup log dbname with truncate only," and check the T-log space again. It has not budged. Still no open transactions.
I also work with Sybase (for a lot longer than MSSQL, actually) and a "dump tran dbname with truncate_only" on a DB with no open transactions clears things out every time. For me that is expected behavior.
Is there anything else I need to do to get that T-log purged out? We have run into problems with the transaction log filling up in the past, and to me this is scary behavior.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

Tom, try NO_LOG option instead of TRUNCATE_ONLY. Help states that those are the same but who knows... The behavior you are experiencing is by design I suspect.

TomCarroll SSC Rookie Points: 35 More actions · Answer 2

I gave the NO_LOG thing a try, but nothing changed.

I should explain why I am playing with this database.

In the past we have had some bad situations. A database with a Simple recovery model and no open transactions should not get jammed up with a full T-log, and if this does happen it should respond to a "backup log" command. But we have found ourselves bouncing the whole server to get a database out of this very jam.

I am not one who is quick to conclude that a software product has a serious bug, but here I am starting to wonder. We are on SP2, by the way.

Thanks for your input!

tulcanla SSCommitted Points: 1541 More actions · Answer 3

yes

I have a huge data warehouse and I make sure that after big loading processes I wil,cheal up my log.

I am sure it is a bug.Especially because this type of behaviour is irregular

murataydin2 SSC Rookie Points: 29 More actions · Answer 4

replace <logname> with log name of the db

and <dbname> with dbname.

if its okey i explain it later.

declare @logname varchar(128)

dbcc shrinkfile ( <logname>, truncateonly )

backup log <dbname> with truncate_only

GO

if exists ( select * from sysobjects where name ='tmp_00000000000001a')

drop table tmp_00000000000001a

create table tmp_00000000000001a (cola varchar(10), colb int )

set nocount on

insert tmp_00000000000001a (colb) values (1)

declare @index int

select @index = 0

while (@index < 4000)

begin

update tmp_00000000000001aset cola = cola where colb = 1

select @index = @index + 1

end

dbcc shrinkfile ( <logname>, truncateonly )

backup log <dbname> with truncate_only

GO

drop table tmp_00000000000001a

GO