July 30, 2018 at 10:08 pm
HI,
We have transactional replication setup in our environment, Recently i installed latest Service pack (SQL2014sp2) to lower environments. After the upgrade we are seeing so many issues with replication. We have 2 publications for same DB (1 publication is using transactional and 2nd one is snapshot) and distribution job's are blocking with each other at subscriber end. Snapshot distribution job is using begin tran command and going to sleep state, it is blocking other distrbution job. I already reinitialized subscription, dropped and recreated the publications, but still we are seeing this issue. Did anyone faced this type of replication issue before or any ideas to resolve this issue, please post ur suggestions.
Thanks
August 1, 2018 at 2:47 pm
Has anyone ever encountered a similar issue?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 6, 2018 at 3:54 pm
krigo33 - Monday, July 30, 2018 10:08 PMHI,
We have transactional replication setup in our environment, Recently i installed latest Service pack (SQL2014sp2) to lower environments. After the upgrade we are seeing so many issues with replication. We have 2 publications for same DB (1 publication is using transactional and 2nd one is snapshot) and distribution job's are blocking with each other at subscriber end. Snapshot distribution job is using begin tran command and going to sleep state, it is blocking other distrbution job. I already reinitialized subscription, dropped and recreated the publications, but still we are seeing this issue. Did anyone faced this type of replication issue before or any ideas to resolve this issue, please post ur suggestions.Thanks
Are the two different publications publishing the same tables or are these different tables?
Did you check the MSsnapshot_history, MSlogreader_history or the MSrepl_errors tables in the distribution database?
How are the agents scheduled? How often is the snapshot agent run and is the log reader agent running continuously?
When you redid the publications or reinitialized, did you do each publication at different times?
Did you check what resource they are locking/waiting on?
Sue
August 13, 2018 at 10:16 pm
Run profiler on your subscriber to see what command it falls asleep on.. create index??
What version is publisher, you w/in 2 versions? When service packing - you need to patch distributor, then pub, then subs in that order.
Chris Becker bcsdata.net
August 14, 2018 at 8:44 am
when i am checking at the locks, i see distribution jobs placing locks on MSreplication_subscriptions table at the subscriber DB. I see there are some duplicate distribution_agent records in that table, so i deleted the duplicate records and we thought it is going to resolve. It resolved in 1 environment but i still seeing this issue in other environment.
Publisher, subscriber and distributor are all on same version ( windows OS and SQL version). I did patching in the same order (distributor, publisher and subscriber).
I checked all replication related tables already, all agents run continuously.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply