Post reply

still trying to distribute transactions from deleted publication

  • September 11, 2006 at 10:07 am

    #173934

    Hi Guys - perhaps a very dumb question.  I am running transaction replication from SQL2kSP4 to another SQL2kSP4 server.

    I setup replication from our production environment last night and found some failures this morning.  It turns out that i created a publication for a table i did not intend to (has an identity field, triggers, etc etc).   I deleted the publication and subscription but it is still trying to distribute the pending transactions and is therefore still failing.  How do I clean this up??

    I prior experience the pending transactions go away when the publication is deleted.

    Thanks!

  • September 11, 2006 at 11:03 am

    #659718

    did you refresh the monitor and see?

    Also you dont have to drop the entire publication for one table. You could drop the table only from the publication:

    exec sp_dropsubscription @publication = 'Pub_Name'

    , @article = 'Article_Name'

    , @subscriber = 'intsql'

    , @destination_db = 'subscriber_db'

    exec sp_droparticle @publication = 'Pub_Name'

    , @article = 'Article_Name'

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • September 11, 2006 at 2:07 pm

    #659759

    Thanks man..

    This publication only had one article (don't ask me why, was scripted by another DBA).

    I refreshed the monitor but it still tries to push the transaction.

  • September 11, 2006 at 3:12 pm

    #659768

    Turned out to be some orphaned transactions.  i cleaned them up manually through the distribution DB.  I'm not sure how this could have happened.  I used the UI to delete the articles.

    delete  from msrepl_transactions where xact_seqno in

      (select xact_seqno from msrepl_commands where article_id not in

        (select article_id from msarticles))

    delete from msrepl_commands where article_id not in

      (select article_id from msarticles)

Viewing 4 posts - 1 through 3 (of 3 total)

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