Dropping Tables for a T-Replication DB in sql 2005

• ALIF-662928

  SSCertifiable

  Points: 6054

  More actions

  January 20, 2009 at 11:14 pm

  #198871

  Hi Guys,

  I have a problem, as you know we cannot drop a table if its being replicated, the only way would be dropping the replication and dropping the tables,

  In my case i cannot drop the replication as we dont have that much downtime to do after hours,

  Is there a way we can drop the tables without getting a new snapshot,

  Or is there a way i can hide those tables in the application as i was told that dropping that tables is that only reason.

  Please advice/suggest.

  Thanks

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  January 20, 2009 at 11:28 pm

  #930114

  You can try the following approach:

  - drop the subscription only for the article that refers to your table. (sp_dropsubscription )

  - drop the article. (sp_droparticle)

  - drop the table.

  Let us know if this works

  -Vikas Bindra

• ALIF-662928

  SSCertifiable

  Points: 6054

  More actions

  January 20, 2009 at 11:37 pm

  #930119

  Hi Vikas,

  The thing is this subscribers are very crucial for us, and creating and synchronizing this subscriber will take many hours in our environment,

  i cannot drop the publisher nor the subscriber.

  thanks

• Old McDonald

  SSCertifiable

  Points: 5768

  More actions

  January 20, 2009 at 11:52 pm

  #930125

  ALIF (1/20/2009)

  ---

  i cannot drop the publisher nor the subscriber.

  thanks

  I am also interested in knowing the alternate solution.

  Anyone?

  -Vikas Bindra

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  January 21, 2009 at 10:02 am

  #930498

  Vikas, You were on the right track. What you wrote is the way to go.

  drop the subscription only for the article that refers to your table.

  This will make sure that if you have a subscriber, it will stop notifying to the subscriber the data change if any for this table.

  drop the article.

  This will make the Publisher to drop the table from its replication list. Once this is done, you are free to drop the table.

  Then you can do a refresh of the Subscription.

  This is quite easy to test. You just need one Desk top machine to test the scenario. Test it out and then try it in production. Please do let us know how your test went.

  -Roy