asked
Cannot drop the table [] because it is being used for replication.
Hi Geeks, We have transaction replication setup (not bidirectional or updatable subscriber) with Publisher A having Subscriber B. The subscriber B is acting as a publisher for six more subscribers. Now, when I tried to reinitialize the subscription for B (with new snapshot as older snapshots were deleted),The replication is failing with error : Cannot drop the table 'table_name' because it is being used for replication. table_name is one of many tables in article being published. Do I need to drop all the subscribers of B (as B is also a publisher) and set the things up from scratch? Need suggestions on workaround or fix. TIA ! Regards, Sajal Bagchi
Set your attributes for the package that you are trying to reinitialize, the article properties, so that the Action if name is in use to Delete Data. This will push the reload throughout your system without dropping the tables or having to remove the replication in the middle.
Sorry, I made a mistake. I misread your question. You are replicating from A -> B and then the same tables from B -> C, D, E, F, G, H My first question would be why not have one distributor going to all servers? In your configuration you will need to drop the package from B out. Then reinitialize. I would still set up the package(s) so that they do not drop and recreate a table if it already exists. Now, the reason for this is because when the table is loaded from the new snapshot, it will be bcp'd in. Replication does not replicate bcp very nicely as it is usually a nonlogged event. Once your table is loaded on B, then you can create everything from B. An alternative would be to turn off your replication packages, import the data to all of the locations. Then recreate your packages telling it that the data is already there. In this way it will not reinitialize and reload everything. This would depend on how much data there is to load and the amount of time plus how hard is the tables hammered on.
There is a requirement of business to have multi layer replication setup ,having subscribers located across different geographic location,may be to mitigate the load from one publisher with multiple subscribers. I will try dropping off all the subscribers of B as suggested and update. Thanks a lot !
18 People are following this question.
