We've faced such a problem after migrating from SQL Server 2008 to SQL Server 2016: there's a merge replication implemented and it takes up to 2 hours (!) to generate a snapshot, instead of 5 minutes like it was before the upgrade.

We've tried to increase MaxBcpThreads, have changed the agents' parameters- but nothing from that seems to help. Would be glad to hear any kinds of solutions.

  • 1
    Try enabling the snapshot agent for verbose agent logging to see what it is spending the most time on, that might give you a clue: support.microsoft.com/en-us/help/312292/… – Brandon Williams Mar 21 '17 at 16:40
  • 1
    we have the same problem ,after we set the COMPATIBILITY_LEVEL to 130 the snapshot generation duration back to normal – saתשלה Aug 8 '17 at 20:40

A few questions about your migration, before suggesting anything.

  • Did you upgrade the install or did you do a fresh install?
  • Did you move to a new machine / hardware?
  • VM or physical box?
  • Did you test snapshot generation on the new machine before doing the migration?
  • New storage or network during the migration?

A wild shot could be this: http://www.sqlservercentral.com/blogs/robert_davis/2009/03/11/Database-Snapshots-Can-Take-a-Long-Time-to-Create/

A person found that the new anti virus was that issue after looking into the same type of issue: https://twitter.com/KenStuber/status/842472176582316032

  • The links you provided are regarding database snapshots. The OP's question is regarding replication snapshots. – Brandon Williams Mar 21 '17 at 16:38
  • Agree - but as I stated, It was a wild shot. I can see your comment on the question, which is a great starting point 😀 – Mötz Mar 21 '17 at 22:39

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.