SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional Replication with growing commands and transactions


Transactional Replication with growing commands and transactions

Author
Message
Muhanned Maayeh-445298
Muhanned Maayeh-445298
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 403
I have setup transactional replication at a customer site with SQL 2008 R1 SP1 Enterprise Edition residing on Windows 2008 R2 Enterprise Edition x64. The replication is configured with one publisher, distributor (on the same engine as Publisher), and subscriber.

Replication is going okay and latency is excellent. And, undistributed commands grows to some amount and returns to zero with estimated time to apply at 0 seconds.

Also, note, I fixed a known bug with SP for distribution cleanup so that the proper amount of commands and transactions are being retrieved for deletion.

However, I noticed that the the number of commands and transactions keeps growing in the distribution database. Currently I show 6,388,422 records in the transactions table and growing and 32,998,193 records in the commands table and growing. The Distributor is configured as default with min and max retention period is 0 & 72, respectively with history at 48.

What I want to know is there a technique, calculations, or guideline to configure the distributor properties? I have Google'd but, what I find is where to configure these values. I am looking on how to change the values in a proper way to tune the distributor.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2525 Visits: 1262
What are you thinking is an issue?

Is it the distribution DB is too big?, with the latency and undistributed commands details you have provided it appears your replication is running as expected.

Reducing your max rentention period will store less within your distribution db, but the longer you have available will mean that in the event the subscriber is down for a sustained period, as long as that period is smaller than the retention period the subscription will not expire.

MCITP SQL 2005, MCSA SQL 2012
Muhanned Maayeh-445298
Muhanned Maayeh-445298
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 403
RTaylor2208 (11/29/2013)
What are you thinking is an issue?

Is it the distribution DB is too big?, with the latency and undistributed commands details you have provided it appears your replication is running as expected.

Reducing your max rentention period will store less within your distribution db, but the longer you have available will mean that in the event the subscriber is down for a sustained period, as long as that period is smaller than the retention period the subscription will not expire.


Hi RTaylor2208,

Thank you for your reply.

You are correct, I don't see an immediate issue at this time but, I do see a continually growing commands and transactions table (However, I am continually monitoring and seeing the counts beginning to come down. Will try to see if there will be an average amount of records for transactions and commands in the meantime.). Yes, I understand that the subscriber will not expire under the given circumstance. I just wondered if there is an optimal way to configure the distributor properties other than accepting the standard default when replication is setup? You are correct in that, at this time I should leave things as is until I need to make a change. Is there an advice you can provide on configuration of such properties?
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2525 Visits: 1262
This thread may be of some use for tips on optimizing the distribution db.

http://www.sqlservercentral.com/Forums/Topic892451-60-1.aspx

MCITP SQL 2005, MCSA SQL 2012
Muhanned Maayeh-445298
Muhanned Maayeh-445298
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 403
Thank you very much I will take a look.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search