October 27, 2016 at 4:54 am
Hi
We have a problem in Transactional replication.
The problem is that I have mismatch number of rows between the publisher and the subscription.
We have Transactional replication in SQL2012 Enterprise edition SP3
Publication – Standalone server
Subscription – AlwaysOn server (Push)
Distribution – 3<sup>rd</sup> server Standalone
The Publisher/Subscriber combination have several publication that all work fine except 1.
The problematic publication has one table.
The table has a column with XML Data type, size – 260GB, 5.5 Millions rows.
The data is not being replicated to the subscriber,
Undistributed commands show 919382 and counting
I think the problem probably in the distribution server.
The repl process try to exec sys.sp_MSget_repl_commands;1 and wait for PREEMPTIVE_OS_WAITFORSINGLEOBJEC. I found that by using sp_whoisactive.
We get error message in distribution job:
Raised events that occur when an agent’s reader thread waits longer than the agent's -messageinterval time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination.
In addition there are no database locks
Please advise how to solve the problem
Thanks & Regards
Erez Hasman
November 7, 2016 at 2:59 pm
How are you accessing AlwaysOn server? by listener? or by primarynodename?
Did you try recreating the subscription?
November 8, 2016 at 4:15 am
By Listner, Only reinitilized
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply