Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional Replication with growing commands and transactions Expand / Collapse
Author
Message
Posted Thursday, November 28, 2013 6:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 5:21 AM
Points: 29, Visits: 273
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.
Post #1518336
Posted Friday, November 29, 2013 5:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:09 AM
Points: 316, Visits: 592
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
Post #1518508
Posted Saturday, November 30, 2013 10:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 5:21 AM
Points: 29, Visits: 273
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?
Post #1518681
Posted Monday, December 2, 2013 3:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:09 AM
Points: 316, Visits: 592
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
Post #1518810
Posted Monday, December 2, 2013 3:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 5:21 AM
Points: 29, Visits: 273
Thank you very much I will take a look.
Post #1518815
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse