Home > Replication > Could not delete the subscription at Subscriber

Could not delete the subscription at Subscriber


Issue: My server “SVR1” runs a SQL Server with a transactional publication. On the second server “SVR2” I had a subscription.
Now the SVR2 does not exist anymore and I want to delete the replication on SVR1. (in the SQL Management Studio, the subscription is still visible)

But if I want to delete the SVR2-subscription on the SVR1, I get the error message 20032 “Could not delete the subscription at Subscriber ‘SVR2’ in database ‘ICSDB’.”How can I delete this subscription (and afterwards also the publication)

I’m using SQL Server 2008 R2 on Windows Server 2008R2

Workaround:

Right click on the publication; go to properties then to subscriptions. Click Delete. This is the EM way. For the QA way, generate the delete statement from EM.

For future use, I was having the same problems described above – but I also had database snapshots on the database that I was trying to remove replication from but that was not mentioned anywhere in the error messages.

Reference Link:http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b64bcde9-4769-45df-a8c7-19606c9c33ca/unable-to-drop-subscription?forum=sqlreplication

Once I removed my database snapshots, the following script cleans things up well:

Example came from here: http://msdn.microsoft.com/en-us/library/ms188411.aspx

DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N’distribution’;
SET @publisher = N’S-ESN-SQL1\SQL1′;
SET @publicationDB = N’AdventureWorks2008R2′;

— Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;

— Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

— Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

— Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

Advertisements
Categories: Replication
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: