Remove article from existing publication, does it require a new snapshot?

  • All the subscriptions failed because there I think there is only on Log reader agent for the distribution database which distributes the records to the subs.

    Don't I have to drop the publication as well or will be ok just by dropping subscriptions and then re-creating them?

  • You're correct, all would fail. My apologies. You will need to do the following;

    1. Stop logreader

    2. Open a new connection to that database in SSMS. (Critical that you are in that database)

    3. Execute sp_repldone

    4. Close the SSMS connection to that database

    5. Start log reader agent

    Let me know if that works.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Again, note, all transactions that have not been replicated will be marked as replicated and you have to do a manual sync.

    Just want to make sure you understand the risks.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • sp_repldone will delete the transactions which are in distributor and didn't make it to subscriber yet. I will those transactions and my replication will be out of sync. Then I will have to sync records manually.

    I think anyways I will have to manually sync the data so your suggestion would work though.

  • One more question- how do I change my filter criteria without dropping subscription or re-initializing?

  • amita pallod (8/25/2011)


    sp_repldone will delete the transactions which are in distributor and didn't make it to subscriber yet

    I believe that it actually resets the LSN but also marks all the transactions in the transaction log that are flagged for replication as replicated.

    From BOL - "Updates the record that identifies the last distributed transaction of the server. This stored procedure is executed at the Publisher on the publication database."

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Hello,

    Even after taking a new snapshot (100% completed status), the subscription shows message 'Initial snapshot for article X is not yet available'.

    Can anyone help me with why do I still receive this message?

    Thanks,

  • I guess it would be good to back up here so that I can understand where you are at in the process with this publication / subscription. Has this been in place for a while? By creating the snapshot are you wanting to reinitialize all the objects / data out to the subsscribers or is this supposed to be a snapshot of a new article going out to the subscribers?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • This is for the existing article. Because the publication and subscriber was out of sync, I took a new snapshot. But still the message doesn't go away 'Initial snapshot for the article is not yet available'

  • Any suggestions / thoughts?

    Thanks,

  • So are you trying to reinitialize the whole publication?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • No I am not. I donot want to. But received this message 'Snapshot is not available'. I am asking why this message is showing even after the snapshot is taken a couple of days ago.

  • He everyone,

    I have read your discoussion about dropping articles and snapshot creation and still have a few questions;

    1. Is this true that after drop an article (sp_dropsubscription + sp_droparticle) I should create new snapshot, but if I create this article (table) on subscriptions- I can safely use my databases without re-initial? So this new snapshot is only for future reinitialize?

    2. After drop and add an article- snapshot is usually marks as "invalid". Where (in sys views/tables) I can find info that snapshot is invalid?

    3. Where (sys views/tables) I can find info that subscriptions is marked for reinitialization?

  • Hello Guys,

    I get this error often.

    'Initial snapshot for this article is not yet available'

    Can anyone help me why I get this error?

  • If you manually start the snapshot agent that error should go away.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 15 posts - 31 through 45 (of 48 total)

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