Replication Issue

Question

Post reply

Replication Issue

Sarwan

SSCommitted

Points: 1999
More actions
June 23, 2010 at 1:49 pm

#140955

Hi All,
Today one of our deveploers updated 600000 records on the publisher database and the update took around 10 minutes to finish but the issue with subscriber its taking forever to update those records and it blocking the users as subscriber is core server for us.
All the transaction are in distributor and they are replicating fast to subscriber ..
Any way to fast up the process ???

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sarwan SSCommitted Points: 1999 More actions · Answer 1

sorry all the trans are in distributor but not replicating fast to subscriber.

Is there any settings I need to change to make it fast ?

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

kiransuram19 (6/23/2010)
Hi All,
Today one of our deveploers updated 600000 records on the publisher database and the update took around 10 minutes to finish but the issue with subscriber its taking forever to update those records and it blocking the users as subscriber is core server for us.
All the transaction are in distributor and they are replicating fast to subscriber ..
Any way to fast up the process ???

Hello,

This might be due to latency in distribution agent .....

do the following:

1. script out replication and check for the @status value in sp_addarticle

if that is set to 0 (zero) that means that it is not replicating as sp's and batch sql statements

--- or -- easy way

select name, status, ins_cmd from sysarticles

check for status column ==> of it is 0 or 8 then it uses TSQL statements

if it is 16 then it uses parametrized statements

2. If you found out status to be 0 or 8 then do following:

exec sp_changearticle @publication =N'[name of publication],

@article=N'[]', ==> name of article having 0 in status

@property=N'status',

@value=N'parameters'

HTH,

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Sarwan SSCommitted Points: 1999 More actions · Answer 3

Thanks.

I followed the Easy method but I dont see any article with status in (0,8) all of them were 57.

Thanks,

Kiran