How to drop subscriptions in Transactional Replication?

Scenario: You are working on refreshing the staging environment by using the database backups from the Production environment. Prior to the database restores, you had removed the subscriptions and publications and then performed the database restores. You had then re-created the publications and tried to subscribe to them. You are greeted by the following error message:

SQL Server could not create a subscription for Subscriber “Subscription_InstanceName”
Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information. Changed database context to ‘MCFIData’. (Microsoft SQL Server, Error: 14058)”

Replication Error

How can you resolve this issue? First of all, you already know that prior to the refresh, replication worked fine. Installing new patches or .NET Framework 4.5 or new or any other software will not solve your problem. Don’t mislead by others. Trust yourself. You will just need to find a way to remove the subscription that you thought you had removed prior to the refresh. Here’s the code that I had used to resolve this issue:

-- Run this on the Publisher
-- Replace the Publishing_DB, Publication_Name, Subscriber_InstanceName, Subscriber_DB accordingly
use Publishing_DB 
exec sp_dropsubscription @publication =N'Publication_Name', @subscriber = N'Subscriber_InstanceName', @article = N'all', @destination_db = N'Subscriber_DB'
GO

Make sure you monitor your publishing instance for blocking while this code to remove the subscription is running and make assessments and kill the blockers if needed or permitted.

Be Sociable, Share!
You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.