[novasql] Re: log file question

  • From: "Clay McDonald" <clay.mcdonald@xxxxxxxxxxxxxx>
  • To: <novasql@xxxxxxxxxxxxx>
  • Date: Sat, 5 Dec 2009 09:03:37 -0500

what does the log_reuse_wait_desc column in sys.databases tell you the log is 
waiting on?

Clay McDonald 

Sent Using BlackBerry


From: novasql-bounce@xxxxxxxxxxxxx 
To: novasql@xxxxxxxxxxxxx 
Sent: Fri Dec 04 10:20:34 2009
Subject: [novasql] log file question 

I'll give this NoVaSQL group email a shot.....apologies for beign long winded.  
But if it were a short question, I'd have answered it myself already.

Problem:  I have a transaction log file that continues to grow, by 1 GB a day 

Background:  Database is in simple mode and has a full backup taken nightly.  
Data file is ~2 GB in size, and is 65% full.  Log file is currently 2.6 GB and 
is 99% full.  Log file is set to grow in 150 MB increments.

The database is a subscriber of a few transactional publications, but not a 
heavy load, < 20,000 transactions per day maybe?  The database also has one 
snapshot publication of one table with one subscriber; snapshot job runs 

The only reason log file is currently 2.6 GB is because I removed and disabled 
replication from this database yesterday; it had grown to 30+ GB.  I've done 
this every couple of months when the log file becomes too big.  After doing 
that, I am able to shrink the log file back to a reasonable 500 MB, where it 
had 1% used.  Then the publication/subscriber was recreated exactly as it had 
been.  Since then it has grown to the point I'm at now.... and will continue to 

DBCC OpenTran returns this:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (135484:22259:1)

Odd considering the database is in simple mode, right?  Reminder, it does have 
a snapshot publication.

DBCC LogInfo returns 127 records with all but the last one as Status = 2 

So, the log file is not being truncated; nightly backups or executing a 
checkpoint does not clear the log.  DBCC LogInfo show that the entire log file 
is Active, so it make sense it isn't being truncated.  And DBCC OpenTran 
apparently has a command somewhere early in the transaction log it wants to 

Why is the active portion of my transaction log so big?  Or why does it appear 
I have a transaction to be replicated?  Something must be set wrong because I 
see no reason why (A) my transaction log should ever grow, being in Simple 
mode, and even (B) why the portion of the transaction log used should ever be 
more then 10%.


Other related posts: