[novasql] Re: log file question
- From: Stephen LaRochelle <slarochelle@xxxxxxxxx>
- To: novasql@xxxxxxxxxxxxx
- Date: Mon, 7 Dec 2009 09:00:35 -0500
I confirmed the log_reuse_wait_desc is Replication.
I did see that blog entry on snapshot replication preventing log truncation
in the past. It definitely describes the situation I have, though I thought
I tried the suggested solution months ago with no luck. Trying again, I did
make the suggested change to Replicate Schema Changes = False.
To clarify, there are *no open* transactions according to DBCC OpenTran.
But the current [Oldest non-distributed LSN] value of (135484:22259:1) is
the same value it was when this growing log/not truncating problem began
last week. The blog entry did say that if that transaction log entry is a
DDL event, then that would confirm the situation of replicated DDL events
not being cleared from the transaction log. But I can't figure out how to
determine what 135484:22259:1 was? How can I pull the transaction from the
transaction log with that info?
I haven't run [EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,@numtrans
= 0, @time = 0, @reset = 1] yet. I'd like to see if the Replicate Schema
Change is the problem first. I assume I'll be running this soon, to at
least temporarily fix the situation. I don't think this will be a permanent
fix though as the real problem hasn't been discovered/confirmed/fixed.
Thanks again,
Steve
PS, Didn't mention this originally, but it is SQL 2005 Standard Edition, 64
bit with SP3. Running on Windows Server 2003 Standard Edition, 64 bit, SP2.
On Sat, Dec 5, 2009 at 10:05 AM, Kun Sik Lee <kunsikl@xxxxxxxxxxx> wrote:
> There are a few factors that can cause that and one of them is log base
> replication such as transactional, merge etc. But I would think if you just
> have snapshot replication, it shouldn't need log base replication. But based
> on the explanation, it does sounds like replication so I agree on
> slarochelle@xxxxxxxxx's <slarochelle@xxxxxxxxx%27s> reply
>
> And if that is related, I would try drop the uncommitted transactions using
> below commends
>
> use <database>
>
> EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time
> = 0, @reset = 1
>
> If it is not related replication, I would also need that from
> log_reuse_waits_desc if it is SQL 2005 or up.
>
> Also, I see that happens if the database has CDC (change data capture)
> enabled or database mirror is enabled as well.
>
> Now, going back to your original email, I see the database actually has
> open tran, that is bothers me so, I would also look if above doesn't fix it.
>
>
> ------------------------------
> From: slarochelle@xxxxxxxxx
> Date: Sat, 5 Dec 2009 09:54:27 -0500
> Subject: [novasql] Re: log file question
> To: novasql@xxxxxxxxxxxxx
>
>
> 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>
> wrote:
>
> what does the log_reuse_wait_desc column in sys.databases tell you the
> log is waiting on?
>
> Clay McDonald
> 202-560-4101
>
> -----------------------------------
> 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
> 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:
