August 16, 2007 at 7:31 am
I have inherited responsibility for a database that seems to have had some replication removed incorrectly. I have a table in our main database that should not have any replication tied to it other than a push subscription from a remote server. I can see the intention there was to take data from the remote server and send it to the main database. During this process I get a cannot drop table error because the table is used for replication. I can see in the system tables what look to be remnants of replication that used to exist but do not appear in the Replication Monitor.
Is there a way of cleaning out any replication remnants without completely redoing replication or is there a way of finding out exactly what is making SQL Server think this table is currently tied to replication?
August 16, 2007 at 8:53 am
You need to drop subscribers before cleaning up the publishser and distributor.
August 16, 2007 at 9:04 am
That is the problem. I don't show any Subscribers or Publishers tied to this table but I do show some remnants in the tables that store subscription and publisher information. neither the folder related to replication nor the replication monitor in Enterprise Manager show any publishers or subscribers to these tables. That is why I am asking how to either identify what is making the system think there is replication in place on these tables or how to clean up any replication remnants that were not removed properly.
August 17, 2007 at 10:57 am
First run "select * from sysobjects where replinfo = 1" to see if replication still exists. If so, run sp_removedbreplication. You might need to enable replication in SQL Server first. Also, you can execute sp_repltrans and sp_replshowcmds. If you get the message that the database is not published, you may need to actually create a publication and then remove replication the right way.
The same thing happened to me before and I tried quite a few things, but it seemed like the key was to enable replication first and go from there. Good luck.
August 17, 2007 at 11:34 am
Doesn't removedbreplication remove all replication from the database?
August 20, 2007 at 2:36 pm
I often have to go digging in system tables and in distribution to do cleanup when there are lingering pieces of replication that don't exist on the other side, therefore can't be cleared through the regular UI options.
Is the whole database still marked for replication with the hand icon? If replication monitor appears clear and that's all you got, you can try sp_resetstatus 'dbname' to reset it, or if that doesn't work, manually set the status in master..sysdatabases to a value of a db that is non replicated, usually 16 or 24.
August 21, 2007 at 2:12 pm
Expand replication folder;
Expand publications folder;
Delete all items under it.
Then, run Disabling replication ...
Hopefully, it will work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply