Distributor Lock Timeouts

I have transactional replication set up and running with a remote
distributor. Replication latency continues to be good (3 - 10 seconds) but
after we added several indexed views to the subscriber, we are seeing lock
timeouts on the distribution database. I haven't been able to tie the
timeouts to any specific activity or activity levels. When I do see the
timeouts I also see a spike in lock requests, but not lock escallations.
Through it all, replication latency seems to stay about the same.
Finally, whenever I fire up Replication Monitor the lock timeouts increase
quite a bit, but they still occur when replication monitor isn't running.
Any thoughts or ideas?
Replication applies the sync commands in a batch and holds a lock while
applying that batch. The locking you are seeing is probably a result of
this. If you are using SQL Server 2005, try using the snapshot isolation
model.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:O76HbG3OIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I have transactional replication set up and running with a remote
>distributor. Replication latency continues to be good (3 - 10 seconds) but
>after we added several indexed views to the subscriber, we are seeing lock
>timeouts on the distribution database. I haven't been able to tie the
>timeouts to any specific activity or activity levels. When I do see the
>timeouts I also see a spike in lock requests, but not lock escallations.
>Through it all, replication latency seems to stay about the same.
> Finally, whenever I fire up Replication Monitor the lock timeouts increase
> quite a bit, but they still occur when replication monitor isn't running.
> Any thoughts or ideas?
>

Distribution CleanUp creates latency

Hi,
We have one publisher, one distributor and two subscriber. We run
transactional replication.
We recently upgraded all our hardware but now we find ourselves with an
unacceptable latency. When the "Distribution Cleanup" process fires it
take up to 4 minutes to run and will cause a complete replication pause
for 1-2 minutes at times.
In a perfect world, latency would always be under 2 seconds. I'll live
with the very seldom latency of 10 seconds.
What can be done to tame the distribution cleanup jog.
Regards,
CanadianGambler
*** Sent via Developersdex http://www.codecomments.com ***
Nothing. You could run the distribution clean up job nightly and see how the
pooled commands affect performance.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Canadian Gambler" <canadiangambler@.hotmail.com> wrote in message
news:%23xLD5HnsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have one publisher, one distributor and two subscriber. We run
> transactional replication.
> We recently upgraded all our hardware but now we find ourselves with an
> unacceptable latency. When the "Distribution Cleanup" process fires it
> take up to 4 minutes to run and will cause a complete replication pause
> for 1-2 minutes at times.
> In a perfect world, latency would always be under 2 seconds. I'll live
> with the very seldom latency of 10 seconds.
> What can be done to tame the distribution cleanup jog.
> Regards,
> CanadianGambler
> *** Sent via Developersdex http://www.codecomments.com ***
|||I guess I should provide more info.
We relocated our equipment room and in the process upgraded the hardware
for all our database servers (1 publisher, 1 distributor and 2
subscribers).
We finished this move 2 weeks ago. The serious latency issue only
started about 6 days ago. We ran the same setup at the previous
location for about 15 months without latency greater than a few seconds.
So something is not quite right. Read on, help is on the way ...
After doing more research last night and this morning we find that some
of the tables that are "suppose to be cleaned up" contain more than
12,000,000 records.
So we are now approaching this problem from a different angle. 1.) Why
are those tables so big and the "clean up" job not "cleaning up". 2.)
How do we go about cleaning those up so that the "clean up job" doesn't
take so long to do it's thing. I saw some similar post so I'm hoping
that I will find some industry wisdom that can help us figure this one
out.
Regards,
Canadian Gambler
*** Sent via Developersdex http://www.codecomments.com ***

Distribution Agent Latency question

I am delivering replicated data from a t3
connection to a t1 connection.. My ping times to
the subscriber are pretty good.. about 40ms
consistant. I have the following rates that seem
to be really slow for distributor/subscribers on
such good connections.
delivery rate (cmds/sec) = 212.0000
latency (msec) = 26890205 <--this seems high
# trans = 12
# cmds = 83784
avg. # cmds = 6982
This latency also seems to be making my
distribution database large on my distributor.
I am using default distribution agent profiles
and it's set to run every 15min.
I don't know - does 7 hours latency sound normal to you?
Latency is occasionally wrong. It reflects the delta between the time the
transaction entered the distribution database and the time it made it to the
subscriber.
If your distribution agent is not continuous the schedule is reflected in
the latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1be9951edb17d9149896d1@.news.newsreader.co m...
> I am delivering replicated data from a t3
> connection to a t1 connection.. My ping times to
> the subscriber are pretty good.. about 40ms
> consistant. I have the following rates that seem
> to be really slow for distributor/subscribers on
> such good connections.
> delivery rate (cmds/sec) = 212.0000
> latency (msec) = 26890205 <--this seems high
> # trans = 12
> # cmds = 83784
> avg. # cmds = 6982
> This latency also seems to be making my
> distribution database large on my distributor.
> I am using default distribution agent profiles
> and it's set to run every 15min.
|||In article <uEryCWJvEHA.3624
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> I don't know - does 7 hours latency sound normal to you?
> Latency is occasionally wrong. It reflects the delta between the time the
> transaction entered the distribution database and the time it made it to the
> subscriber.
> If your distribution agent is not continuous the schedule is reflected in
> the latency.
>
Anything I should start looking at to resolve why
there is such high latency for this one
subscription only? I have the log reader set to
run continuous and the dist. agent to run every
15min.
tia
-comb