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.
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.