Sunday, March 25, 2012

Cannot drop the table "xyz" as it is being used for replication

I have a replication setup running on 2 Win2K Server PCs with SQL
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison

No comments:

Post a Comment