none
Transactional replication is not working properly RRS feed

  • Question

  • Hi, 

    In Transactional publication when i want to add the articles in replication through SQL Server 2014 Management Studio like 1500 table, 3000 store procedures,400 functions and 200 views. But when i going to add then SQL Server stops responding and it gets hanged ,even after 15 hours articles were not added.Is there any solution to add the so much articles in replication so that it can work smoothly.

    Regards,

    Suresh Kumar

    Thursday, November 2, 2017 1:17 PM

All replies

  • You are probably encountering blocking.

    A scripted solution would likely work better for you.

    Try to create a publication which has one table, one stored procedure, one function and one view. Then using what you script out as a template, create a publication which contains everything.

    Another option for you is to script out all of your objects you wish to replicate and then run it on an empty database. Then create the publication there, script it out and then run the publication script in the actual database.

    • Proposed as answer by Hannah Yu Monday, November 20, 2017 4:02 AM
    Thursday, November 2, 2017 2:33 PM
    Moderator
  • Hi Suresh Kumar,

    >> But when i going to add then SQL Server stops responding and it gets hanged

    This might be a SQL Server problem, could you please check your SQL Server error log or Event Viewer to see if any error message there? If so, share the error log with us via onedrive so we can better understand it. Do mask sensitive data before uploading.

    Also, it might be an issue with SSMS, try to add articles with SSMS 17.3 see if it helps.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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.


    • Edited by Hannah Yu Tuesday, November 28, 2017 9:45 AM edit
    Friday, November 3, 2017 8:11 AM
  • There is also option at the end of New Publication Wizard that instead of creating Publication you can script it out. 

    Agree with Hilary here that scripts will work much better and not try to add all in one go. We noticed that on very busy systems especially when adding/dropping subscriptions for ALL articles. We ended up dropping articles one by one otherwise we get locked.

    Monday, November 6, 2017 2:46 PM