0

I had 2 subscriptions to a publication, but they were from the same subscriber server and writing to the same subscriber database, just one was bad and one was running fine.

enter image description here

enter image description here

I could not find a way to delete the bad one, so I decided to drop both, and then recreate the subscription.

on the publisher server

use [SupplierDB]
go
exec sp_dropsubscription @publication = N'SupplierDB', 
@subscriber = N'REPLON1', 
@destination_db = N'SupplierDB', 
@article = N'all'
GO

That dropped the good subscription.

enter image description here

Because I could not find a way to drop or even see the bad subscription I decided to drop the publication, but when I try it I get the following error message:

-- Dropping the transactional publication
use [SupplierDB]
exec sp_droppublication @publication = N'SupplierDB'
GO

Msg 14005, Level 16, State 1, Procedure sp_MSdrop_publication, Line 75 Cannot drop the publication because at least one subscription exists for this publication. Drop all subscriptions to the publication before attempting to drop the publication. If the problem persists, replication metadata might be incorrect; consult Books Online for troubleshooting information.

How can I find and drop this subscription?

This is a problem that I originally had on another question: NoSync subscriptions will need to be dropped and recreated

  • You may need to look at the actual catalog table(s) and clean them up. I'm not as familiar with Transactional Replication as I am Merge, but in my experience the GUI and stored procedures often don't cleanup the underlying tables properly. Try selecting on MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See:msdn.microsoft.com/en-us/library/ms179855(v=sql.105).aspx – tommyfly Jul 31 '15 at 10:41
  • on the distributor, there is indeed. select * from MSsubscriptions WHERE publisher_db = 'SupplierDB' this shows my rogue subscriber there – Marcello Miorelli Jul 31 '15 at 10:50
  • delete the row(s) and it should resolve the problem. You may need to set sp_configure 'Allow Updates' = 1 to make changes to the table(s), I'm not sure. – tommyfly Jul 31 '15 at 10:53
  • Would you mind confirming my answer? – tommyfly Aug 5 '15 at 18:30
2

You need to look at the actual catalog/metadata table(s) and clean them up. The GUI and stored procedures often don't cleanup the underlying tables properly. So, after you have run the sp_dropsubscription SP run a select from MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See: msdn.microsoft.com/en-us/library/ms179855(v=sql.105).aspx. Delete any row(s) representing subscriptions that shouldn't be there and it should resolve the problem. You may need to set sp_configure 'Allow Updates' = 1 to make changes to the table(s).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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