Post reply

NoSync subscriptions will need to be dropped and recreated

  • September 28, 2005 at 10:10 am

    #89310

    Transaction replication issue (Windows 2000, SQL Server 2000 SP3)

    I have one published article subscribed by multiple subscribers (push). One of the subscribers (read only) has data modified. Replication for that subscriber is marked inactive.

    I run start synchronize the subscription but same error message. 

    "The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."

    What are my options?

    1. Just drop that one subscription for that article and recreate  it?

    2. Reinitialize all the subscirptions for that article?

    I would appreciate your comments on it.

    Thanks

     

  • September 28, 2005 at 5:51 pm

    #593879

    Your problem is the Distribution Server settings!

    One of the properties of the Distribution Server is how long SQL Server will wait until it inactivates Subscriptions due to the frequency of transactions.

    Right click the Distribution properties and increase the value.

    Kindest Regards,

  • September 28, 2005 at 9:28 pm

    #593896

    I have it as never expires.

  • September 29, 2005 at 4:01 am

    #593965

    As the message states, you will need either to resync the subscription or if it has been setup with the NoSync option, drop and re-create the subscription.

    Unsure about your last statement. Don't believe you can set the transaction retention period to never expire.

    Paul R Williams.

  • September 29, 2005 at 11:51 am

    #594135

    General tab on publication property has subscription section. One of the options is "Subscriptions never expire". 

    Sorry for the confusion. I had to drop the subscription and recreated successfully. Issue is now resolved.

     

    Thanks

    HT

  • September 29, 2005 at 7:09 pm

    #594233

    I think you will find that although your publication didnt have the expiration, the distribution database probably did!

    If you go to 'Replication Monitor', right click, select 'Distributor Properties', select your distribution database (probably distribution), select 'Properties'. You will see the transaction retention in the distributor database.

    What will have happened is your client database could not find its pointer back into the distribution database and would have issued the above error. The only way to correct this is to drop and resubscribe the client.

    The solution is to get your clients to resynchronize (I'm assuming they are demand driven rather than timed) before the data is lost in the distribution database.

    Hope this helps,

    Gareth

    Kindest Regards,

    Gareth

  • May 27, 2008 at 7:30 am

    #820551

    But once you drop it, you lose the unreplicated transactions. Basically, you are screwed. You will have to resynch the data and create the subscription again.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply