none
Snapshot Agent Times Out At SP_MSACTIVATE_AUTO_SUB RRS feed

  • Question

  • Hello.

    I have a Transactional Replication with a remote Distributor, on a DB that I was previously publishing, then removed the PUB and SUB (because I switched to a Remote Distributor), and now am trying to set the DB for Replication up again.

    For the past 10 hours the Snapshot Agent re-tried several times, and it has always gotten stuck at the same point (82% completion):

    Execution Timeout Expired....Command Text: sp_Msactivate_auto_sub (with Params @publication, @article = % and @status = active)

    I have now put it to 3600 seconds before it times out, but regardless if this works of not, I would like to kindly ask the following:

    1. Any clue why exactly it has been failing at this Stored Procedure, and what does this SP do?
    2. If I end up having to drop the whole Publication and start anew, I am thinking of adding a new SP to my "Clean Up a Publication" set of SPs, and it's sp_cleanupdbreplication, so I wanted also to kindly ask what does it exactly do and should I fire it up on both PUB and SUB (and Distributor), or only PUB Server?

    (I did read a bit about it, but from what I've skimmed, the documentation is a bit obscure, so I thought I'd ask here for a clear answer)

    With thanks and kind regards,

    Bogdan


    • Edited by BogdanU Friday, August 9, 2019 12:24 PM
    Friday, August 9, 2019 9:22 AM

Answers

  • Hi BogdanU,

     

    Would you please refer to the metadata of  sp_MSactivate_auto_sub and sp_cleanupdbreplication?

     

    >>what does it exactly do and should I fire it up on both PUB and SUB (and Distributor), or only PUB Server?

     

    It is used to clean up the articles for this publication, and to delete the row. It is also used to clean up any traces in other system tables. You only need to execute it on the PUB.

     

    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

    • Marked as answer by BogdanU Monday, August 12, 2019 7:52 AM
    Monday, August 12, 2019 7:00 AM

All replies

  • Hi BogdanU,

     

    Would you please refer to the metadata of  sp_MSactivate_auto_sub and sp_cleanupdbreplication?

     

    >>what does it exactly do and should I fire it up on both PUB and SUB (and Distributor), or only PUB Server?

     

    It is used to clean up the articles for this publication, and to delete the row. It is also used to clean up any traces in other system tables. You only need to execute it on the PUB.

     

    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

    • Marked as answer by BogdanU Monday, August 12, 2019 7:52 AM
    Monday, August 12, 2019 7:00 AM
  • Hello, Dedmon.

    Thank you for the helpful reply.

    With kind regards,

    Bogdan

    Monday, August 12, 2019 7:52 AM
  • You should not need to run this proc. Clearly the snapshot agent is hanging. it is possible there is a user process which is blocking with the snapshot agent. You will need to determine exactly what that is.
    Monday, August 12, 2019 3:47 PM
    Moderator
  • You might want to remove that article where it is hanging from your publication to see if this solves it. Then put the article in its own publication.
    Monday, August 12, 2019 3:48 PM
    Moderator
  • Hi, Hilary.

    Thank you for the helpful replies.

    Up until now I have not had any more issues with this, so I guess (and hope) it was a one-time thing.

    With thanks and kind regards,

    Bogdan

    Tuesday, August 20, 2019 3:07 PM