Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 4 databases, a publisher and 3 subscribers (transactional replication).

In case of an error in the publisher I want to:

  1. delete all subscriptions
  2. create a new publisher
  3. make the two remaining databases subscribe to the new publisher.

(so that the program can continue normally and the 3 databases continue up to date)

I have the code to create a new publication and to create a new subscriber, but I don't know how to remove the old subscription.

I've read this Here:

At the Publisher on the publication database, execute sp_dropsubscription (Transact-SQL). Specify @publication and @subscriber. Specify a value of all for @article. (Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.

At the Subscriber on the subscription database, execute sp_subscription_cleanup (Transact-SQL) to remove replication metadata in the subscription database.

but im my case, publisher and distributor are on the same machine. (and so I can't execute sp_dropsubsription right away, because the publisher machine is down)

edit: I've done sp_subscription_cleanup @publisher='PCONE', @publisher_db='TEST' (in one of the subscribers) but the subscription still is in the local subscriptions folder

and so this does not prevent the old publisher to send data when he comes back to life.

How can I prevent that? (how can I programmatically do the equivalent to right click a subscription and select delete)

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

If you were looking for the big hammer, sp_removedbreplication is it. This should do what you are asking, and remove all of the replication related goodies in the subscribing database, regardless of whether or not the publisher is accessible. Once the publisher/distributor come back up you can do the cleanup to remove the subscription on that side as well.

Like I said, this is the BIG HAMMER, so use it carefully.

share|improve this answer
what should I care particularly? what can go wrong with this? –  RagnaRock Dec 4 '11 at 21:59
Well for one, that proc removes ALL replication info, and is not reversible. So if that database has multiple subscriptions or other publications, they will all be dropped. –  Tevo D Dec 5 '11 at 2:50
some how after restarting the server machine the subscription apear under local subscriptions after executing sp_removedbreplication. But the subscription is not working as I wanted to :) –  RagnaRock Dec 5 '11 at 10:34
Now that the server is back online, this is where the cleanup comes into play... although, are you saying that the subscriptuion from the original server reappeared on the subscriber? Did you reinitialize the subscription from the publisher when it came back up? –  Tevo D Dec 5 '11 at 13:26
I just restarted the (virtual) machine –  RagnaRock Dec 12 '11 at 18:17
add comment

Since its the publisher that pushes the data, removing the subscription is done at the publisher. Here's a script I use (Taken from Wrox). I first find the subscription I want to remove in part 1, and then use that information to drop the subscription. This script isn't meant to be run all at once.

**/* Part I on the publisher/distribution server */**
use distribution

/* Declare a table variable */
declare @subscription_push table
publisher_id smallint,
publisher_db sysname,
subscriber_db sysname,
subscription_int int,
sync_type tinyint,
status tinyint

insert into @subscription_push
select publisher_id, publisher_db, subscriber_db, subscription_type, sync_type, status
from distribution..MSsubscriptions
where subscription_type = 1 and status = 2

/* check the data */
select * from @subscription_push

/* Declare table variable that will store the publisher, the publication database, the type of publication and the name of the publication using
sp_helpsubscription */

declare @subscriberinfo table
(publisher sysname,
subscriber sysname);

insert into @subscriberinfo
select publisher, subscriber from distribution..MSsubscriber_info

/* check the data */
select * from @subscriberinfo

**/* Part II */**

/* Then remove the subscription from the publisher based on the information in the table @subscriberinfo*/
exec sp_dropsubscription @publication='publication_name', @article='article or all for all articles', @subscriber='subscribername', @destination_db='TEST'


Hope this helps. I have used in a test environment several times. Perhaps someone has a better way to remove push subscriptions?

share|improve this answer
The problem is that I want to do this while the publisher is down, to keep the system running with a new publisher and to prevent the old one to corrupt the data when he comes up again. –  RagnaRock Nov 28 '11 at 12:10
How do you remove a subscription from a publication, if the publisher is down? If it were a pull subscription, maybe the publisher could be down. You did however say it was a push subscription, so the subscription is configured on the publisher. I do wonder though, if the subscriber database retains any information about participating in a subscription? –  Craig Efrein Nov 28 '11 at 14:41
my objective is to stop the subscribers to receive any data from the old publisher. I tested this using Microssoft sql management studio, by right cliking in the subscription and choosing delete. This stops them from being updated from that subscription. But I want a progammatical way to do this, so that a c# program can manage this automatically. –  RagnaRock Nov 28 '11 at 14:46
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.