locked
Subsription not working RRS feed

  • Question

  • Hello Forum

    We have a number of Databases that have some of the data replicated to other systems using the Publication/Subscription method.

    A couple of days ago, another user truncated one of the Tables in a Subscriber Database and now the Replication is failing.

    I tried to recreate the snapshot last night using SSMS, (Remote session onto the Server, launched SSMS, started the process, disconnected from the Server).  however when I logged back in a few hours later my session had been automatically logged off and as such the Snapshot was not recreated.

    Is there a TSQL Script which I can use to recreate the snapshot?

    This action is what I want to script: -

    Navigate to Replication\Local Publications  
    Right-click the publication {Publication name} and click View Snapshot Agent Status.  
    A Dialog Box is displayed entitled "View Snapshot Agent Status - Publication_Rep" , click <Start>


    Please click "Mark As Answer" if my post helped. Tony C.

    Friday, March 9, 2018 9:42 AM

Answers

  • You need to mark the subscription for reinitialization first.  Then run snapshot.  The snapshot does not do anything unless it thinks it needs too.  It doesn't know you truncated the table on the subscriber.

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:14 AM
    Friday, March 9, 2018 12:51 PM
  • Use this stored procedure:

    sp_startpublication_snapshot

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:14 AM
    Friday, March 9, 2018 4:47 PM
    Answerer
  • Thanks everyone

    Unfortunately the subscription was too badly damaged, so I ended up recreating the Subscription over the weekend and this has resolved the issue.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:21 AM
    Monday, March 12, 2018 11:15 AM

All replies

  • You need to mark the subscription for reinitialization first.  Then run snapshot.  The snapshot does not do anything unless it thinks it needs too.  It doesn't know you truncated the table on the subscriber.

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:14 AM
    Friday, March 9, 2018 12:51 PM
  • Use this stored procedure:

    sp_startpublication_snapshot

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:14 AM
    Friday, March 9, 2018 4:47 PM
    Answerer
  • Thanks everyone

    Unfortunately the subscription was too badly damaged, so I ended up recreating the Subscription over the weekend and this has resolved the issue.


    Please click "Mark As Answer" if my post helped. Tony C.

    • Marked as answer by Anthony C-UK Monday, March 12, 2018 11:21 AM
    Monday, March 12, 2018 11:15 AM