Unable to reinitialize all articles in a subscription RRS feed

  • Question

  • Hello,

    I'm a Jr. DBA at a software company and we use transactional replication (push) for one of our BI products.  Currently, one of our subscriptions has been "marked inactive and must be reinitialized."  I've tried reinitializing the subscription through the GUI (i.e., Replication Monitor --> right-click and select "reinitialize all subscriptions", choose "use a new shapshot" (all other options greyed out), choose the one to reinit, etc.)  but it isn't working.  When I view the details of the Snapshot Agent I'll see that the job ran but the action message listed says, "A snapshot was not generated because no subscriptions needed initialization" so it obviously did not reinitialize it.  I've also tried running the stored procedure sp_reinitsubscription on the publisher database with the following syntax:

    EXEC sp_reinitsubscription
      @publication = '<publication name>',
      @article = 'all',
      @subscriber = '<subscriber sql server>',
      @destination_db = '<subscriber db>'

    When I run this it completes without errors but displays the message "Cannot reinitialize article '<table>' in subscription '<subscriber server:db name>' to publication '<publication>' (subscribed with the 'no sync' option)." and the problem is still unresolved.  Is there any way I can fix this without having to drop and recreate my subscription?  I'd really not have to do that as it would need to be done during a maintenance window (which is a pain).

    Any help is much appreciated!

    Friday, October 18, 2013 1:04 PM

All replies