February 15, 2012 at 9:28 am
Hi All,
I have a weared situation here. I see a publication "Pub1" on SQL 2005 server. There is no subscription to that at all. I do not see it under the "Pub1" at Publisher and I also checked at Subscriber and it doesnt exists there too. However there is another publication (different name) with the same articles exsits for the same Publisher Subscriber pair and it works fine.
when I run this it runs successfully.
EXEC PublisherDb.sys.sp_dropsubscription
@publication = N'Pub1',
@article = N'all',
@subscriber = N'SubscriberServer',
@destination_db = N'SubscriberDB' ;
GO
and then when I run this it fails with below error:
exec sp_droppublication @publication = 'Pub1'
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.
when I try to delete Publication using SSMS then it fails saying:
Could not delete publication 'Pub1'. Couldnt not drop article, A subscription exists on it. Changed database context to 'PublisherDb'.
Is this because the same articles are replicated using another Tx Replicaiton or something else? Can anyone please suggest anything?
February 15, 2012 at 9:30 am
One more thing to mention here:
When I check replication Monitor for this Publication Under Distributer to Subscriber History it says The process was successfully stopped. And under All subscriptions tab it says Not Running, Expiring Soon.
February 15, 2012 at 9:41 am
Can you run this on your publication database
select * from syssubscriptions
There is a column dest_db..just see what you got?
M&M
February 15, 2012 at 9:56 am
Are there other publications for that database that you want to keep? The query that the previous poster is helpful. I would include a bit more to get a better picture though....
selectss.*
fromsyssubscriptions ss inner join sysarticles sa
on ss.artid = sa.artid
inner join syspublications sp
on sa.pubid = sp.pubid
wheresp.name = 'Pub1'
This will reveal the metadata for that publication in the syssubscriptions table. If there is a reference there then that is what is holding you up.
Are you trying to remove the publication via script or GUI? If script have you tried the GUI? There are some benefits in using the GUI in that it connects to the subscriber and will clean up the metadata there for you as well, else you have to do that via script as well.
Back to my earlier thought, if you have no other publications on that database you can use sp_removedbreplication to clean ALL publications off that DB for the replication type specified. Again, only use this when you are sure you have no other pubs on that database.
Let me know how you make out.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2012 at 10:08 am
Thanks for the quick help.
1. I do have 10 other publications on the server and atleast 6 out of them for the same database. So I think sp_dropdbreplication is ruled out here.
2. I executed above query on Publisher and it doesn't return any result.
3. I have tried removing this publication using GUI first and that didn't work so I tried script but that also doesnt work.
February 15, 2012 at 10:13 am
So you see a publication, Pub1 on the publisher but don't show a subscription. When you query syssubscriptions on the publisher you return 0 rows. What happens when you query the subscriber database?
select * from MSreplication_subscriptions where publication = 'Pub1'
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2012 at 10:17 am
One other thought here, can you open the publication, remove the article from the publication and then drop the publication?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2012 at 10:18 am
when I run above query on Subscriber database then also it doesnt return any result.
February 15, 2012 at 10:24 am
I also tried opening the publication properties and removing articles one by one but it fails saying can not drop article, subscription exists on it.
February 15, 2012 at 10:29 am
...and when you run the following you show the tables in the publication?
selectsa.*
fromsysarticles sa inner join syspublications sp
on sa.pubid = sp.pubid
wheresp.name = 'Pub1'
When you ran the first query that I gave you ran that in the publication database and put in the publication name, correct? 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2012 at 10:31 am
Yes I made sure I ran the first query you gave in Pub db and put the pub name.
when I run this
selectsa.*
fromsysarticles sa inner join syspublications sp
on sa.pubid = sp.pubid
wheresp.name = 'Pub1'
Yes, it gives me article names.
February 15, 2012 at 10:58 am
Have you tried running the sp_dropsubscription and passing in a specific article name instead of "all"? Please try that and let me know.
If this doesn't work you have a metadata corruption issue as far as I can tell. You can try to clean that out manually but your best bet would be to contact MS CSS for that.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2012 at 11:23 am
Yes I alrady tried that. And tried again now it gives below error.
Msg 14055, Level 11, State 1, Procedure sp_MSrepl_dropsubscription, Line 522
The subscription does not exist.
February 15, 2012 at 2:33 pm
Definitely appears that your metadata is corrupt. My recommendation would be to call MS and get support from them.
Let me know how things work out.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply