none
Drop rogue Subscription issue. RRS feed

  • Question

  • Greetings. We're having an issue dropping a Subscription on the Subscriber database, even though the Publication has already been removed from the Publisher. We're not sure how we wound up here, and it's not causing any issues, but would like to remove it regardless. 

    When attempting to drop it on the Subscriber we get the message "Cannot drop the procedure dbo.sp_msdel_myTable because it is being used by replication" . The Publication database itself has been removed from the Publication box, so running sp_dropSubscription is not an option. I'm aware of what this sproc is and what it's used for and can tell I have 160 other sprocs/ dependencies that will get in the way based on the query below. 

    select *
    from sys.objects
    where type in ('p')
    and name like 'sp_msupd_dbo%'
    or name like 'sp_msdel_dbo%'
    or name like 'sp_msins_dbo%'
    order by name

    I know that all of these guys are for Subscriptions, not Publications. However, it's also extremely important to note that this "Subscription" database is now a Publication database. It has 2 Publications with Subscriptions to another box. 

    All said I'm 99% sure I can remove all of these sprocs without wrecking anything for the Publications on this DB(?), but was hoping to get a second opinion on it.

    Thanks!


    Thanks in advance! ChrisRDBA

    Monday, March 19, 2018 6:33 PM

Answers

  • Hi,

    The way you can make it more "proper" way, is using SSMS. I understand that after removing Publication u still have entries in msrepliction_subsriptions on Subscriber? If yes then also in SSMS when you go to Replication->Subscriptions you should see entries regarding subscriptions. Now just right click and choose delete. IT will clean up all your replication procs as well. Just tested on my 2014 VM and it works fine.

    Before:

    While deleting you will get that message:

    After nothing left:

    • Marked as answer by ChrisRDBA Tuesday, April 24, 2018 9:59 PM
    Tuesday, March 20, 2018 8:31 AM

All replies

  • Replication generates these stored procedures on the PUBLISHER. I would not remove them.
    Monday, March 19, 2018 6:49 PM
    Moderator
  • Replication generates these stored procedures on the PUBLISHER. I would not remove them.

    Hillary, not sure if you recognize my username or not, but you've helped me on several occasions over the years with replication. If fact everyone on my team at work has heard me me say you probably have forgot more on the topic than I'll ever know (and I know a fair amount)

    Because of this it is with great hesitation that I question your above comment. 

    Please see this link and the paragraph below:

    By default, transactional replication propagates changes to Subscribers through a set of stored procedures that are installed on each Subscriber. When an insert, update or delete occurs on a table at the Publisher, the operation is translated into a call to a stored procedure at the Subscriber. The stored procedure accepts parameters that map to the columns in the table, allowing those columns to be changed at the Subscriber.

    I also just looked at my Prod Publication and Subscription DB's, these sprocs only exist on the Subscribers. 

    Not trying to sound rude to someone I'd consider a mentor, so please don't take it as such.


    Thanks in advance! ChrisRDBA

    Monday, March 19, 2018 11:33 PM
  • I was so convinced you were wrong, I had to whip up a quick repro.

    create database ChrisRDBAPub
    GO
    create database ChrisRDBASub
    GO
    exec sp_replicationdboption ChrisRDBAPub, publish, true
    GO
    use ChrisRDBAPub
    GO
    sp_addpublication ChrisRDBAPub, @status=active,  @alt_snapshot_folder='c:\temp', @snapshot_in_defaultfolder=false
    GO
    sp_addpublication_snapshot ChrisRDBAPub
    GO
    create table test(PK int identity primary key, charcol char(20))
    GO
    sp_addarticle ChrisRDBAPub, test, @source_object=test 
    GO
    sp_addsubscription  ChrisRDBAPub, 'ALL',@subscriber=@@Servername, @destination_db=ChrisRDBASub
    GO
    sp_startpublication_snapshot ChrisRDBAPub
    GO
    WAITFOR DELAY '00:02:00'
    GO
    select * from ChrisRDBAPub.sys.objects where type='P'
    GO
    select * from ChrisRDBASub.sys.objects where type='P'
    GO
    You are correct!

    Monday, March 19, 2018 11:53 PM
    Moderator
  • Fair enough sir! We'll let it slide this time considering all the times you've helped me over the last 12-14 years. ;-)

    Anyways, if you (or anyone) knows the answer to my original question, I'd appreciate it.



    Thanks in advance! ChrisRDBA

    Tuesday, March 20, 2018 1:50 AM
  • Hi,

    The way you can make it more "proper" way, is using SSMS. I understand that after removing Publication u still have entries in msrepliction_subsriptions on Subscriber? If yes then also in SSMS when you go to Replication->Subscriptions you should see entries regarding subscriptions. Now just right click and choose delete. IT will clean up all your replication procs as well. Just tested on my 2014 VM and it works fine.

    Before:

    While deleting you will get that message:

    After nothing left:

    • Marked as answer by ChrisRDBA Tuesday, April 24, 2018 9:59 PM
    Tuesday, March 20, 2018 8:31 AM
  • Thanks, but that's not whats happening for me. See my original post for a complete description.

    Thanks in advance! ChrisRDBA

    Tuesday, March 20, 2018 2:15 PM
  • as far I see you asking if you can drop these replication procedures? And I am saying that most probably you also still have under local subscriptions entries for these old subscriptions. I am showing you how you can remove these replication procedures in cleaner way (by deleting subscription) then dropping object by object.

    On the other hand you also saying you getting error when trying to drop these procs?

    "When attempting to drop it on the Subscriber we get the message "Cannot drop the procedure dbo.sp_msdel_myTable because it is being used by replication" 

     And later us asking if you can drop it without breaking anything?

    "All said I'm 99% sure I can remove all of these sprocs without wrecking anything for the Publications on this DB(?), but was hoping to get a second opinion on it."

    I believe you could drop these procedures and it wont break anything (even if the database is published now as the replication procedures are not stored as an object on Publication database just on subscriber). However I would not recommend that.


    Tuesday, March 20, 2018 2:56 PM
  • as far I see you asking if you can drop these replication procedures? And I am saying that most probably you also still have under local subscriptions entries for these old subscriptions. I am showing you how you can remove these replication procedures in cleaner way (by deleting subscription) then dropping object by object.

    On the other hand you also saying you getting error when trying to drop these procs?

    "When attempting to drop it on the Subscriber we get the message "Cannot drop the procedure dbo.sp_msdel_myTable because it is being used by replication" 

     And later us asking if you can drop it without breaking anything?

    "All said I'm 99% sure I can remove all of these sprocs without wrecking anything for the Publications on this DB(?), but was hoping to get a second opinion on it."

    I believe you could drop these procedures and it wont break anything (even if the database is published now as the replication procedures are not stored as an object on Publication database just on subscriber). However I would not recommend that.


    Got it. I'm aware dropping the sprocs won't drop the subscription, and that I'll still need to do it manually. Attempting what you're doing results in a different outcome for me. Not sure why, but it does, so your method doesn't work for me.  Thanks.


    Thanks in advance! ChrisRDBA

    Tuesday, March 20, 2018 3:10 PM
  • I also just stumbled on this, but still have the same concerns. 


    Thanks in advance! ChrisRDBA

    Tuesday, March 20, 2018 3:11 PM
  • Hi,

    The way you can make it more "proper" way, is using SSMS. I understand that after removing Publication u still have entries in msrepliction_subsriptions on Subscriber? If yes then also in SSMS when you go to Replication->Subscriptions you should see entries regarding subscriptions. Now just right click and choose delete. IT will clean up all your replication procs as well. Just tested on my 2014 VM and it works fine.

    Before:

    While deleting you will get that message:

    After nothing left:

    So I finally got back to work on this issue, and this post was inadvertently the answer for me. I had tried dropping the sprocs, but but got an error b/c they're being used for replication. But I'd also try to drop the Publication, and couldn't b/c the sprocs existed. 

    Rereading this thread I noticed your comment about the "MSreplication_subscriptions" table still having data in it -- something I hadn't considered as I was focused solely on the sys% tables. I deleted the ghost subscription entry value in that table, and was then able to delete the ghost Subscription. 

    Thanks!


    Thanks in advance! ChrisRDBA


    • Edited by ChrisRDBA Tuesday, April 24, 2018 9:59 PM
    Tuesday, April 24, 2018 9:58 PM