SQL Server Performance

Log in or Sign up

2005 Transactional Replication Performance Problems - help please

Discussion in 'SQL Server 2005 Replication' started by ColSchmoll, Sep 27, 2007.

ColSchmoll New Member

I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?
Help please.

ColSchmoll, Sep 27, 2007

#2
ColSchmoll New Member

This was caused by a trigger on one of my replicated tables. It total killed my distibution agent performance, once removed it latency went from 20 mins to 3 seconds in about 10 seconds!!! Any idea why these triggers worked fine in SQL 2000?

ColSchmoll, Sep 27, 2007

#3
satya Moderator

If the application requires triggers at the Subscriber, the triggers should be defined with the NOT FOR REPLICATION option at the Publisher and Subscriber.
See Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION. This ensures that triggers fire only for the original data change, but not when that change is replicated.

satya, Sep 27, 2007

#4

(You must log in or sign up to reply here.)

Share This Page