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)


3 Answers 3


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.

  • what should I care particularly? what can go wrong with this?
    – RagnaRock
    Commented Dec 4, 2011 at 21:59
  • 1
    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
    Commented Dec 5, 2011 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
    Commented Dec 5, 2011 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
    Commented Dec 5, 2011 at 13:26
  • I just restarted the (virtual) machine
    – RagnaRock
    Commented Dec 12, 2011 at 18:17

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?

  • 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
    Commented Nov 28, 2011 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? Commented Nov 28, 2011 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
    Commented Nov 28, 2011 at 14:46

I came across this old post looking for the same answer. There is an easier way without torpedoing all replication. After running sp_dropsubscription you must run a cleanup stored procedure at the subscriber. So, after running this:

USE PublisherDB
exec sp_dropsubscription
    @publication = N'MyPublication', 
    @subscriber = N'SubscriberServer', 
    @destination_db = N'SubscriptionDatabase', 
    @article = N'all';

You will need to run this:

USE SubscriberDB
exec sp_subscription_cleanup
    @publisher = N'PublisherServer', 
    @publisher_db = N'PublisherDB', 
    @publication = N'MyPublication';

I hope this helps someone else. This removes any metadata from the subscriber. Until the cleanup is run the subscription will show up in Local Subscriptions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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