none
Applying snapshot not working (generation batches) RRS feed

  • Question

  • Hi

    I have a filtered publication setup on a database and I am looking to setup a new subscription on a brand new PC. I am able to create the new subscription on the new PC and generated the required snapshot - a full snapshot first and then one for the data partition due to the filters on the publication. Filters on the publication include only pulling down data to the new subscriber for the last 1 month.

    When I then go to apply the snapshot onto the new subscription, it will get so far and then start to run through generation batches and will go as high as 91k before then timing out. It appears as if the snapshot is not being applied correctly, is there something I am doing wrong?

    I have setup replication for other databases during the years and never experienced this same issue.

    Any help would be greatly appreciated.

    Saturday, October 19, 2019 9:10 AM

All replies

  • Hi SQL Noob88,

     

    Would you please connected to the Publisher, expand Replication, Local Publications, Select the publication you are referring to and expand, right click on the subscription select "View Synchronization Status" and let me know what you see via ssms. Also right click on the publication and select "View Snapshot Agent Status" and provide that information.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 21, 2019 7:17 AM
  • Hi Dedmon

    Thanks for coming back to me. I have checked each of the sections as request - see below.

    Would you please connected to the Publisher, expand Replication, Local Publications, Select the publication you are referring to and expand, right click on the subscription select "View Synchronization Status" and let me know what you see via ssms.

    I have setup a brand new subscription on the PC in question, when I right click on the Subscription I can't see 'View Synchronization Status', only Properties? Is this what you are referring to? I am using SQL 2012.

    Also right click on the publication and select "View Snapshot Agent Status" and provide that information.

    I have done this, and can see the following:

    Agent Type: Snapshot Agent
    Publication: NewPub2018
    Publication database: TestingDB
    Duration: 03:47

    Let me know if you need any further information,

    Thanks,


    Monday, October 21, 2019 12:43 PM
  • These commands that are coming down are rows in msmerge_contents and tombstone which are not in the snapshot files, so they need to be processed after the snapshot is applied.

    The best thing to do is generate the main snapshot and then generate the snapshot for the filtered publication. The snapshot files will not contain very many values for msmerge_contents and will be applied faster.

    Another option is to send the files to the subscriber and use the

    -DynamicSnapshotLocation 

    parameter to apply them locally.

    Monday, October 21, 2019 3:34 PM
    Moderator
  • Hi Hilary

    I did generate the main snapshot and then a snapshot for the filtered publication data partition. When I try to then apply this, that is when the generation batches come into play.

    Thanks,

    Mark

    Monday, November 4, 2019 4:57 PM
  • Did you try setting -QueryTimeOut to something large on your snapshot agent?
    Monday, November 4, 2019 5:03 PM
    Moderator
  • Yes, it is currently set to 60000 as per other replicated subscriptions that we have setup in the past. Should we set this to be something higher?

    The last attempt that it started doing generation batches for, was running for around 3 days before it eventually timed out?

    Monday, November 4, 2019 5:42 PM
  • 3 days? That is a very long time. how large is the database? Have you checked for blocking while the snapshot is running? Also how many subscribers do you have?
    Monday, November 4, 2019 6:07 PM
    Moderator
  • Have you tried running trace when your snapshot is applying?  Also, what command is not applying on the subscriber?  I have experienced scenarios in which the PK was not built and commands were attempting to apply.  Also check the performance of the partition stored procedure.  My guess is that something is failing. 

    David z

    Tuesday, November 5, 2019 2:48 PM
  • The main publication DB is around 70GB however I have added filters to the publication so that when Im creating a new subscription it is only pulling down data for the last month.

    On the current publication we have 4 other subscriptions and these are replicating without any problem. Do you think setting up a new publication would be the best solution?

    I'm not sure how to check for blocking while the snapshot is running, is there any documentation or steps online on how to do this?

    Thanks!

    Wednesday, November 6, 2019 9:38 AM
  • I haven't no - how do I run a trace? Do you mean using the SQL Profiler tool? The snapshot just keeps running and will keep running through generation batches for a few days and then will eventually time out.

    How can I check the performance of the partition stored procedure?

    Thanks!

    Wednesday, November 6, 2019 9:42 AM
  • Yes, run profiler on the subscriber with the exception event of the Errors and Warnings collection to see if you can catch the error. 
    Tuesday, November 12, 2019 5:00 PM
    Moderator
  • Thanks for coming back to Hilary. Unfortunately I have been pressed for time so in the end up I have had to create a new filtered publication from scratch.

    I've created a new subscription which took around 1 hour 18 mins to complete and didn't go through any replication batches at all. Very strange!


    Thursday, November 14, 2019 10:29 AM