[novasql] Re: log file question

  • From: Stephen LaRochelle <slarochelle@xxxxxxxxx>
  • To: "novasql@xxxxxxxxxxxxx" <novasql@xxxxxxxxxxxxx>
  • Date: Sat, 5 Dec 2009 09:54:27 -0500

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

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

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

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

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 replicate?

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: