latency on transaction replication

I am using transactional replication for a 100GB database that has 2 nosync
subscribers. The pull subscriptions are segmented into like table groups.
They all use a custom profile for the distribution agents that I tweaked
over time to minimize latecy. In the last month I have noticed that 1 of the
subscriptions always has a higher latency in EM then the others. After
looking into several things, we also found out that there was a
hardware/firmware problem with Dell, so we ahve applied that patch and it
has fixed a number of other problems in the DB, but still not the latency
problem on this one subscription. Some of the other subscriptions have much
bigger tables and data changes, so I am at a loss of why this one is causing
me so many problems. I frequently will have to apply a snapshot of this
subscription because it will get so far behind, and then will slwly build
back up again until another snapshot is required.
Can anyone help?
Here are some of the current configs of the custom profile:
BcpBatchsize: 100000
CommitBatchSize: 1000
CommitBatchThreshold: 5000
PollingInterval:1
Thanks,
Kristy
Well nevermind for now. Seems like it's finally leveling off today after I
redid the subscription last night. All day it's been getting smaller (now
down to 10 minutes and the others being in the seconds); where as before it
would just get bigger and bigger (up to days behind.)
I suspect that in addition to the firmware updates and reindexing some
corrupted indexes have helped. But I am just guessing.
--Kristy
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:eeniL84RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> I am using transactional replication for a 100GB database that has 2
nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of
the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have
much
> bigger tables and data changes, so I am at a loss of why this one is
causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>

Latency In Transactional Replication

During last couple of weeks some new Tables & Fields were added. SO both the
time i have to re-intialize the Replication. my question is about LATENCY
where i have seen for both the subscriber(SUB) for SUB-1 is 3.5 Hours &
SUB-2 is 21 hrs. Size of the PUBLISHER DB is 18GB. AS Latency are in
Millisecs i have mentioned in HOURS for better understanding. i want to know
how i can resolve this issue as every time when new table or fields are
added it takes 21-22 hours to complete Replication Re-intialization process.
Any help will be highly appreciated. Thanking you in advance.
Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
changing a PK.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||TWO new Tables were added with unique key for couple of fields & were
not going to be use in Transactional Replication due to limitation that
each table needs to have at least one PK. and this 2 tables as no PK.
So using sp_repladdcolumn will not help, i maybe wrong.
please correct me if i am wrong as i am not SQL expert. again any help
& to correct me will be appreciated.
Paul Ibison wrote:
> Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
> changing a PK.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear Mr. Paul:
I have replied back with details & did see your
feedback. i will appreciate if you please kindly reply me at your
earliest & doing so will be highly appreciated.
thanks
Paul Ibison wrote:
> Now I'm confused - if you're adding tables to a publication this is
> supported and adding columns to an already published article is supported,
> but this seems to be not relevant to your case somehow. Is the problem that
> you want to add tables which don't have a PK? This is definitely
> unsupported, but you already know that, so please can you add a few more
> details about what it is that you want to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Large SQL update - effect on SQL 2005 transactional replication

I'm a newbie to Replication and recently setup the following.

Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.

Any feedback greatly appreciated.

Thanks

You shouldn't have any problems, just understand that if you do this in one large transaction, logreader agent won't pick up changes until the transaction has been completed/committed, and the distribution agent will not pick up the changes until after the logreader agent has completed.

large MSReplication_queue table?

Hi All,
I have a queued update Transactional Replication with a push subscription
from the publisher. The subscriber's database size is nearly double of the
published database on the publisher side. Further investigation found that
the MsReplication_queue has some old data (about last 6 months). Is there any
agent that is supposed to clean the table? Or I have to clean the table
manually? Can someone help me plz?
Thanks in advance,
has the distribution agent run recently? How about the distribution clean up
agent?
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:BC9B7884-A1CD-4E59-BFB8-9917F35F64E4@.microsoft.com...
> Hi All,
> I have a queued update Transactional Replication with a push subscription
> from the publisher. The subscriber's database size is nearly double of the
> published database on the publisher side. Further investigation found that
> the MsReplication_queue has some old data (about last 6 months). Is there
> any
> agent that is supposed to clean the table? Or I have to clean the table
> manually? Can someone help me plz?
> Thanks in advance,
|||Hilary,
The distribution agent and distribution clean up agent (both on the
Publisher side) are running and the data is being pushed from the Publisher
to the Subscriber. The Subscriber is a DR site and sometimes it runs
production traffic. The MSReplication_queue table is on the Subscriber side.
Does the distribution cleanup agent on the Publisher clean up the
MSReplication_queue table on the Subscriber side? What side effects if I
manually delete entries in the MSReplication_queue since they are old data.
Thanks,
"Hilary Cotter" wrote:

> has the distribution agent run recently? How about the distribution clean up
> agent?
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:BC9B7884-A1CD-4E59-BFB8-9917F35F64E4@.microsoft.com...
>
>