Remove Replication:

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.


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.

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.

—————————–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)

sp_removedbreplicaiton ‘PublisherDB’

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.

 –end 22112010



Twitter @thakurvinay

This entry was posted in Replication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.