2

I was looking for dropping a subscription, however, I made the mistake of dropping it in the subscribe, without dropping it at the publisher first, ignoring the warning from the picture below.

enter image description here

TITLE: Microsoft SQL Server Management Studio

This action will remove the selected subscription information, but not the previously replicated data, from database 'MY_DATABASE_Sub'. Removing the subscription information should be done only if the subscription has been deleted at the Publisher, MY_PUBLISHER, or if the subscription is otherwise defunct. Removing an active subscription will result in errors at the Publisher.

Are you sure you want to remove the subscription information?

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17119.0&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CleanUpPushSubscriberConfirmation&LinkId=20476

------------------------------ BUTTONS:

&Yes &No

that is causing me the error below (in yellow), basically the subscription is showing up in the replication monitor, but it should not be there at all.

enter image description here

Question is: How can I fix this error? Basically get rid of the ghost subscription showing up in the replication monitor and causing it become red flagged?

  • See if my answer helps you. – Kin Shah Oct 13 '17 at 16:10
  • @Kin that is very useful too, but this is slightly different as I want to keep the other subscriptions, and drop only the one that has errors. – Marcello Miorelli Oct 16 '17 at 15:08
1

Always drop a subscription in the publisher first to avoid this error.

But if you happen to drop the subscription in the subscriber and get the error in the question above, the way I sorted it out was to go to the publisher server and publisher database and run the script below, that generates a drop subscription command for each article still pointing to the wrong subscription.

this is the script to generate the script to run:

-- check the server and database
-- these should be the publisher server and publisher database

SELECT @@SERVERNAME
-- CTSTGDB1

SELECT DB_NAME()
-- APIASTG_Repl_Pub

-- go to the publication database
USE APIASTG_Repl_Pub
GO

--check how things are (works for before and after)
sp_helppublication


sp_helpsubscription



--script to generate the drop of all articles in the BANDIT subscription
USE APIASTG_Repl_Pub
GO

select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
, s.dest_db
,SQL_='exec sp_dropsubscription ' + CHAR(13) + 
      '     @publication=' + '''' + db_name() + '''' +  CHAR(13) + 
      '    ,@article='  + '''' + SA.NAME + '''' +  CHAR(13) +             
      '    ,@subscriber=' + '''' + UPPER(srv.srvname) + '''' +  CHAR(13) +       
      '    ,@destination_db=' + '''' + s.dest_db + '''' 
            +  CHAR(13) + CHAR(10) 

from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
where s.dest_db = 'MY_WRONG_SUBSCRIPTION'

-- get the _SQL generated above and open a new query, 
-- on the same server, same database, run it
-- problem should be solved!!!

Below is an example - a partial list of the script generated after running the script above.

exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Airport'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_ArrivalCalendar'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation_Details'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'  

The script generated should be run in the publisher (server and database)

this is the picture from the replication monitor: note on the right hand side before there were 2 lines, one with a red error cross on it, that was the subscription we wanted to delete and now it has been deleted: enter image description here

there are still errors on this replication monitor, but they are unrelated to this subscriptions as they are on a different server.

I would like to include a link to the question below because it might be useful when you have problems deleting a publication

SQL Agent still attempting replication for deleted publication

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.