Its quite some time that I blog… but good news is I have some good stuff to blog on… so could be you will see some good blog in pipeline soon.

I blog on how to configure the replication here with commands and a separate blog for replication setup here.

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.

or

sp_dropdistributiondb 'distribution'

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.

http://support.microsoft.com/kb/324401

This will also solve the issue with following error:

Cannot drop the distribution database ‘distribution’ because it is currently in use.

-----------------------------Process 2

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.

http://msdn.microsoft.com/en-us/library/ms173516.aspx

-----------------------------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.

http://vyaskn.tripod.com/repl_ans3.htm

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.

Thanx.

Vinay

Twitter @thakurvinay