August 17, 2010 at 2:11 pm
Hi Folks,
I'm testing replciation between a 2005 publisher and subscriber. I dont think it matters, but it's set to allow SQL 2000 subscribers too (since I will eventually add one in). I set both publisher and subscriber to simple recover model for my testing.
During the initialization of the subscriber, it runs through the tables, it gets to one particularly active table and the transaction log at the subscriber grows up over 20GB. Is there some setting in the profile I can set to limit the amount of transactions it sends before it commits?
August 18, 2010 at 12:51 pm
Have you considered setting the recovery mode on the target to simple?
August 18, 2010 at 1:35 pm
Both databases are set to simple mode (as indicated in the original post).
August 18, 2010 at 1:56 pm
I don't think you can really do anything about the batch size with merge replication
August 18, 2010 at 4:48 pm
There must be some way to govern how many changes it makes before committing a transaction...
August 18, 2010 at 5:01 pm
NJ-DBA (8/18/2010)
There must be some way to govern how many changes it makes before committing a transaction...
Open Replication Monitor and look at the Subscription Watch List tab for the publisher in question > in the drop down select "Show Merge Subscriptions" > Right click on the subscription you are interested in and select Agent Profile. You can use those to change your batch sizes. Can't guarantee that is going to solve the log bloat but that should give you a start.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 18, 2010 at 6:19 pm
Thanks David- will give that a shot. Will cut it in half and see what that does to the TLog size.
August 18, 2010 at 6:26 pm
NJ-DBA (8/18/2010)
Thanks David- will give that a shot. Will cut it in half and see what that does to the TLog size.
Post back with how this goes and what settings you settled on. Might be helpful to others. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 18, 2010 at 7:13 pm
Will do...
August 19, 2010 at 9:41 am
In order to ensure identical factors for this experiment, Shrank the transaction log file to 50MB (autogrowth on, growth factor 500MB) on both publisher and subscriber deleted the subscription and the snapshot and recreated them both.
I left the profile with default batch size of 100000. As expected, the subscriber transaction log grew to almost 14GB- the same as my previous test.
I then repeated everything- shrank tlog, deleted the subscription and snapshot and recreated them both. This time, I set batch size to 5000.
The result was the same- 14GB Tlog growth with both databases in simple mode.
So it seems that batch size does not have any impact on when the replication transaction is committed. I am convinced there must be some way to change this- mostly because I know there is such an option in SSIS which uses the same processes.
August 19, 2010 at 11:27 am
I think the batch size setting only applies to replicated transactions not the initialization....
August 19, 2010 at 11:33 am
Yeah- that looks to be the case... but what about the initialization- there must be some way to control the amount included in a single transaction. In my case the largest .bcp files is less than 2GB, but the transaction log hits almost 15GB.... this is not one of our larger databases so I can't imagine how one would initialize a subscription using a snapshot for larger databases.
August 19, 2010 at 11:48 am
Setting up replication for large objects can be fun...
Are you replicating indexes? You may want to try replicating without creating the indexes (it will force the primary key I think).
August 19, 2010 at 12:04 pm
Yes, I'm including indexes- because of the nature of the application, it is a requirement.
August 19, 2010 at 12:05 pm
This isn't a "real" problem- there will be plenty of room for the transaction log to grow on the subscriber... it's more of a theoretical annoyance for me now... I'm just baffled that MS would not build in some kind of method to force a commit during initialization to prevent huge amounts of transaction log growth at the subscriber.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply