[novasql] log file question
- From: Stephen LaRochelle <slarochelle@xxxxxxxxx>
- To: novasql@xxxxxxxxxxxxx
- Date: Fri, 4 Dec 2009 10:20:34 -0500
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
nightly.
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).
Questions:
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%.
Thanks,
Steve
Other related posts:
