I have two servers, SQL Server 2012.

I have snapshot replication set up. Pull subscription. Publisher, distributor, same box, subscription is a different box. Lately, I have been running into some issues with this. Usually, the replication finishes the entire process in 2 hours and completes the bulk copy on subscriber.

Lately, there have been delays and it seems that replication hangs and a message pops up on Replication monitor saying that it is waiting for a response from the server. It always hangs up for 3 hours, tries to get going, hangs up for another 2 hours and finally finishes up in the morning. Each time it has delayed, it follows the same pattern.

I ran whoisactive to find if any process blocks our snapshot repl but did not find any. Checked the processors and memory but they are nowhere near 100% when it is blocked. I have 16 processors and 32GB ram on the box. Would like to add that MADOP is 8 and cost threshold for parallelism is set to 50.

In my research, I have found out people saying that it hangs when bulk copying larger tables which is true but then again, why does it hangs only 10% of the times and runs fine the other 90%. Any insight is truly appreciated.

  • This may seem promotional, I assure you it's not. But have you run sp_blitz on the machine yet? – robopim May 22 '18 at 10:46
  • Are these servers on the same box? If so, have you tried push over pull? I've always achieved better consistency with pull replication. – robopim May 22 '18 at 10:48
  • No different boxes, I will install sp_blitz – Jacob May 22 '18 at 11:01
  • Let me know the results! REPL can be a real job, I mean joy haha! – robopim May 22 '18 at 11:04
  • 1
    Are you creating a lot of indexes as part of the snapshot? I've seen this happen when the replication process creates multiple indexes on large tables. One thing you can try is to have it create the clustered index only and set up a SQL job to create the non-clustered ones. – Alen May 22 '18 at 13:51

Your Answer

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

Browse other questions tagged or ask your own question.