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!

• mromm

  SSCarpal Tunnel

  Points: 4301

  More actions

  December 16, 2002 at 6:14 pm

  #444557

  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

  December 17, 2002 at 9:04 am

  #444596

  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

  December 18, 2002 at 2:13 pm

  #444763

  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

  December 19, 2002 at 3:27 am

  #444787

  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