I had an unforeseen issue with a sub going down (generic error, was probably a connection issue) and now that I've reconnected the sub it is insanely slow re-syncing. It seems to download about 1000 changes then sit there for about an hour before adding another 1000.

sub snapshot

Any ideas why this is going so slowly? This is just one table of dozens and I feel like this won't catch up before Monday (when I have users meant to be coming online).

Any help is appreciated! Thanks!


It's been running for over 24 hours now and is slowly chewing through the records but at a still very slow pace. It now toggles between the shot above and 'Enumerating changes in filtered articles using Subscriber's assigned partition ID'. Yes, there is a filter in place but it's been there since I first set up replication and has never caused an issue before.


When I go into Replication Monitor there is an error:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

But what is strange is that there is zero system activity and I have another subscriber on this publication working as well a secondary replication from the same publisher to the same subscriber that is working perfectly. I've tried stopping and restarting, taking and applying a fresh snapshot but nothing seems to work. There are no blocked processes, no active queries that are deadlocking or anything else I can think to check.

To be clear:

  • Publication A => Sub 1: WORKING
  • Publication A => Sub 2: NOT WORKING
  • Publication B => Sub 1: WORKING
  • Publication B => Sub 2: WORKING

If anyone has any help, I'd much appreciate it.

  • Can you stop and restart the process? Jul 2, 2016 at 9:17
  • This is actually my second time doing it already. Last time I stopped it after 12 hours of not completing. It's been going on for 24 hours now and is slowly chewing through tables.
    – tmwoods
    Jul 2, 2016 at 17:39
  • Indexes are rebuilt every 24 hours (that is the best trade-off I found for how quickly they frag). I haven't updated statistics in a while but can't see it slowing things down this much. And what do you mean by 'clean up'?
    – tmwoods
    Jul 2, 2016 at 19:23
  • They don't get severely fragmented in 12 hours; I rebuild them every 24 hours because it's cheap and I have virtually no load on the servers in the middle of the night. But would indexes cause replication delay like this? I feel like that isn't the issue and I can't find any literature suggesting it would be.
    – tmwoods
    Jul 3, 2016 at 16:18
  • For what it is worth, it sounds like something is broken. (Ha!) Have you considered dropping Sub 2 and then rebuilding the subscription from scratch. If there is a corruption in the subscription, this may clear up the problem.
    – RLF
    Jul 4, 2016 at 19:31

1 Answer 1


There are many factors that can affect replication. Some common factors that I've come across are related to slow networking, blocking and storage issues. But the one that may be a factor is the performance problem with virtual log files. If you are initializing a database with a large amount of data and the default growth factors are in place, then sql server will grow the data 1mb at a time and the log 10 percent at a time. To check for the VLF issue, run dbcc loginfo. If that command returns over 100 records, I would be concerned. If it returns thousands of records, then it will have a real impact on performance. There are plenty of articles written on this subject. The basic fix is to adjust the autogrowth settings on all data and log files to reasonable sizes, then shrink the log file and initialize it back to the original size. I would check all of the databases, including the distribution database. There could also be other reasons why the distribution database is slow to distribute the transactions. I've experienced this on several transactional replication systems in the past and was suprised at the impact of correcting this every time.

I would also suspect locking at the publisher. Identify the spids involved in the replication and use the dynamic management view sys.dm_os_waiting_tasks to determine which waits are involved in these sessions. This will help identify what these subscriptions are waiting on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.