July 11, 2002 at 9:39 am
Has anyone come across a problem with shrinking the log under transactional replication?
I currently have a fairly large DB in SQL 2000 that is being partially replicated to another SQL server and the log will not shrink (either manually or automatically on full backup) unless I run a couple of scripts to force the log to shrink after stopping (or removing) replication. To me it seems very silly to have to go through this every time I need to shrink the log.
Any thoughts on this would be greatly appreciated.
July 11, 2002 at 9:45 am
Is the log reader running continuously? Transactions cant be cleared from the log until the log reader processes them.
Andy
July 11, 2002 at 9:59 am
Yes, it is. And as far as the log reader, that is one of the scripts I have set up for this database (to force it to completion).
I was hoping that someone might know of a trick to getting around this. It's kind of a kink in my day when I have to bang away with the replication process every week or two because the log has swelled up to 10GB.
For me it's hard to believe that MS would have set trans replication up without coming up with a solution to a swollen log.
July 11, 2002 at 10:51 am
In general there is no reason for the log reader not to complete. Is it stopping due to timeouts, or getting behind because of large or large numbers of transactions? Verified that you have the -continuous switch on the log reader?
Other than that, are you sure that replication is the problem? Having the log grow could be because of maintenance activity like index rebuilds, large transations, etc. Getting a 10g log file to shrink can take a while because the virtual segments are so big. In addition, if you're needing 10g on a recurring basis, why not just leave the space allocated?
I'm using transactional in production with no issues.
Andy
July 11, 2002 at 12:41 pm
The Continuous switch is set on the log reader agent. But, correct me if I'm wrong, I want to have it set to continuous if I am looking for Real Time replication to another database.
As far as the loads we look at for transactions, out heavy hits are on Fridays but even then we aren't looking at a massive deluge of activity.
Also I need to rephrase part of this problem. This database isn't that large. The database that is being replicated to is very large, and it is this destination DB that I am having the problem with.
Sorry for the confusion, I started this thread low on caffeine.
July 11, 2002 at 2:22 pm
Yes, you should normally have the -continuous flag on. Im confused about the rest. You're saying its the subscriber log that is unshrinkable? That shouldnt have anything to do with replication - all trans repl does is send commands just like they were sent to the publisher. SQL applies them and marks as done, after that it depends on your recovery mode what happens.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply