0

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 – 3rd 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.

TT.
  • 15,774
  • 6
  • 47
  • 88
Erez Hasman
  • 1
  • 1
  • 1
    Damiano, it is a bit vicious comment and you are not giving advice on top of it. This is a first question for the user and you can understand him for formatting it too much, since there are lots of comments on formatting and phrasing. – Liya Tansky Oct 30 '16 at 12:10
  • I just did some reading on that wait type and it can present when the process is waiting on a reply from a client. That is, it could be your subscriber agent that's causing the issue. Do you know what the value for CommitBatchSize and CommitBatchThreshold are for the distribution agent? Do you see commands being delivered (through profiler or something like that) at the subscriber? – Ben Thul Oct 30 '16 at 17:12
  • Several questions:
    Is it replicating slowly or not at all?
    Are you seeing other errors in the replication monitor?
    How much memory does the distributor have?
    How are the drives configured?
    What is the disk latency on the distributor for msdb and tempdb
    [(sys.dm_io_virtual_file_stats)](http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/)     – MikeL-Atlanta Oct 30 '16 at 15:31
  • The replication is not delivered commands, monitor show distributor to subscriber is pending. Server memory 64GB, Disks 64KB, CommitBatchSize and CommitBatchThreshold are defaults values, AvgBPerRead 69771 AvgBPerWrite 12208 AvgBPerTransfer 66649 – Erez Hasman Oct 31 '16 at 09:12

1 Answers1

0

Have you tried enabling output logging on the distribution agent? That should tell you in detail exactly where the agent is getting held up.

How to enable replication agents for logging to output files in SQL Server

SQLDBA
  • 1