[novasql] Re: log file question

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

Not sure if this would help but..


Clay McDonald 

Sent Using BlackBerry


From: novasql-bounce@xxxxxxxxxxxxx 
To: novasql@xxxxxxxxxxxxx 
Sent: Sat Dec 05 09:54:27 2009
Subject: [novasql] Re: log file question 

If I remember correctly; replication. Can confim Monday but am 99% sure. Thanks

On Dec 5, 2009, at 9:03 AM, "Clay McDonald" <clay.mcdonald@xxxxxxxxxxxxxx> 

        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 maybe?  
        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 grow.
        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 (active).
        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: