I might have discussed about how to remove replication on my earlier blog but would like to have separate blog for future reference so blogging this again about how to remove the replication, I know its very important to remove the replication properly.
Replication removal steps:
—————————–Process 1 –replication clean up on server
1. Remove the subscriber.
a.Go to Replication –>selection Publications->select Publisher, you will get the respective subscriber(s) for that publisher. select subscriber and right click –>delete
2 Remove the publisher.
Go to Replication –>selection Publications->select Publisher –>Right click and delete
after this I generally enable none on
Go to Replication –>–>selection Publications->Right click –>configuration of publisher, subscriber or distributor and enable none for all.
3 Remove the Distributor.
Go to Server Property-> Select Replication tab –> select “Disable” button –> this will open a wizard. go through the wizard and disable the distribution. it will drop the distribution database.
exec master..sp_dropdistributor – this will remove the replication monitor as well and logins
at the end I generally run
sp_removedbreplication ‘replicated database’ – to make sure we have completely remove the replication
Their is a great KB article which describe it in details.
This will also solve the issue with following error:
Cannot drop the distribution database ‘distribution’ because it is currently in use.
exec master..sp_dropdistributor @no_checks = 1
please use it with care as it will not check anything and will delete the distributor corresponding to that replication(subscriber/publisher/distributor including logins). at the same time its very fast, so if you want to just clean everything from the server specially on dev this is good.
—————————–Process 3 –check
Today I was working on Transaction replication on Sql server 2000 sp3a… found very weird thing happened I removed replication with above commands no replication onto the server but I was still getting below error:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table ‘test1’ because it is used for replication.
I searched a lot but could not find much information, I also tried to detach the db and attached on different server but no luck.
finally found that “replinfo” column from “sysobjects” table
update sysobjects set replinfo=0 where replinfo=1
Thanx to Vyas for the great article.
—— 01282010 also Today want to remove replication so followed below process (this is also i worked on Sql server 2000 SP 3a)
This is remove the publising the publisher db, but I could see the snapshot, logreader and distributor jobs into jobs as well as replication monitor is RED X.
I run the following command to rectify that.
select * delete from dbo.MSdistribution_agents where publisher_db=’PublisherDB’
select * delete from dbo.MSlogreader_agents where publisher_db=’PublisherDB’
select * delete from dbo.MSsnapshot_agents where publisher_db=’PublisherDB’
select * delete from dbo.MSpublications where publisher_db=’PublisherDB’
—— end 01282010
I know you might have read the same in my blog but this will be one point for removal of replication and try to keep up to date or keep it with series.
—— edit 22112010
found a blog by -Chris Skorlinski to remove the replication for older version of sql server like sql server 2000.