sql server replication: Hey. I've my merge agent to run on continous. I make some change at the
publisher and wait for it to replicate over but it takes about 9 to 10
minutes to replicate over. Can someone tell me why it takes so long? What are
the steps involved after the change is made to the publisher side. a flow
chart kind of thing.
where does the change go between the publisher and before the change
actually hits the subscriber?

I've ran profiler on the subscriber side and tried to see what it does. Well,
there is one table on which it keeps trying to create a merge repl trigger on
one table for some reason, as soon as some changes are made on the publisher.
I have no clue why it does that. And it takes exactly 10 minutes to get 1
change replicated in my case. I dont think it's a network problem because
some changes from sub to pub r quick. It's only this update on a table which

In Merge History Table, it says
Merged 64 data changes (60 inserts, 0 updates, 4 deletes, 0 resolved
conflicts).

Is there any table where I can find the actual transactions that happened in
this 60 inserts and 4 deletes? Is there anyway I can find more information
about this? The reason I'm asking this is because I didn't do anything like
this. I just updated one row and that's it. Please let me know about this.

Its kind of difficult to explain, basically a trigger is fired. This trigger
will write an entry to MSMerge_Contents if its an update or an insert, and
MSMerge_tombstone if its a delete. Then the merge agent runs. First off it
checks to see if it needs to update any identity values and if it needs to
it does. Then it updates gen_cur in the sysmergearticles table. Any DML
occurring on the subscriber at this time will have a different generation
value and will not be processed as part of the batches which form the sync.
Then the merge agent checks to see if the previous sync was interrupted. If
it was interrupted it gathers all the unprocessed entries in
MSMerge_Contents and MSMerge_Tombstone and ups their generation value.

It then figures out what rows in MSMerge_Tombstone and then MSMerge_Contents
to send to the publisher. It check msrepl_info for this info. It then sends
these rows on a article basis to the publisher. The publisher then gathers
together all rows for the partition that would be sent to the subscriber. A
partition is rows in MSmerge_Contents and MSMerge_Tombstone which are not
filtered out by the filter clause if any.

It then compares the rowguids coming from the subscriber with the rowguid on
the publisher and figures out what is missing on either side and fires procs
to push them on whatever side does not have them. Primary key collisions may
occur at this point and if so are logged to the conflict tables. Updates and
Delete conflicts are detected by having the same GUID values in the
publisher batch and subscriber batch. These rows are logged to the conflict
tables and rolled back according to the conflict resolver mechanism you have
employed.

After the sync process has completed the next batch is processed and the
process repeats itself.

The reason your synchronization takes so long is difficult to say. Run
profiler and see what is going on in your database on the publisher and
subscriber. It could be a network problem. You could be bumping into
replication non-convergence.

--
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

[quoted text, click to view]

Tejas,
this script needs a bit of work to take som eexceptions into account, but
pls give it a go in its current state:
http://www.replicationanswers.com/Script9.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

When I look at the profiler after the change was made at the publicher, I see

SP:StmtStarting -- del_92A80137542643EE8651935FDBAF223B
create trigger del_92A80137542643EE8651935FDBAF223B on
[dbo].[datHardwareComponents] FOR DELETE AS
Replication Merge
Agent eSMART_Cluster ASAP\eSMART_Cluster 5072 60 2005-11-22 11:23:18.823


Now, what I dont understand is that the
--del_92A80137542643EE8651935FDBAF223B is one the triggers created by
replication for one of the edited tables. But y is there a "create trigger"
statement following it? There is a lot of these statements because this
update at the publisher touches a few tables. So, this happens for every
table it touches. And it takes approx 10 minutes to finish these 60 inserts
and 5 deletes which is not acceptable.
Meanwhile, can you please tell me if there's something to worry if i catch
that statement in the profiler when the updates were made? Thank you.

I'll take a look tomorrow if no-one has replied before that.
Paul Ibison

It means the trigger is being executed. Nothing more, nothing less.

Check this out
create table test
(pk int not null, charcol char(20))
create trigger testtrigger on test
for update
as
print 'test'
insert into test (pk, charcol) values(1,'test')
update test set charcol ='test1'

and run profiler with statement starting.

I'm wondering if perhaps you are doing cascading updates or deletes, or have
other triggers hanging off this table. Could you perhaps script this table
and all related user triggers and paste it here?


--
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

[quoted text, click to view]