locked
Mysterious Subscription - Merge Replication RRS feed

  • Question

  • Hi. I have SQL Server 2005 Standrad Editions publisher and 3 subscribers.

    I had a publication with 3 subscriptions. For some reason i had to delete one of the subscription.

    At the time of deletion for the first time on the publication server, it didnot return any errors.

    Then when i tried to recreate it at the publisher, it gave me message that it already exists.

    Now when i try to delete it at the publisher (both by seleting the check box to connect to subscriber n deleting or unchecking the check box), it fails saying there is no such subscriber.

    I can go to the subscriber server, under replications and subscritpuions, i can delete it. But after few minutes or so, it shows up again automatically.

    In replication monitor i donot see that particular subscription under the publication

    In the Distribution database, i donot see the entry of the subscriber in MSmerge_subscriptions table

    But in the database on which replication is taking place, i do see the jargon entries of the subscriber in the System table sysmergepublications.

    When i delete those entries with delete statements, they show up again in the table after a few minutes.

     

    I ran SQL server profiler to try and understand whats happening. My suspect is its something to do with the execution of the stored procedure exec sp_MScreateglobalreplica after which the entries in the tables shows up again.

    Does any one has any clue for me on how i can clean up my replication setup from this ghost subscription?? I am trying to troubleshoot this since a few days now!

    Tuesday, October 12, 2010 10:58 AM

Answers

  • Yupp...i got an answer. The below forum helped me in my mysterious query on which i spent weeks for troubleshooting

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/df79fe81-9b5e-4726-88d9-d4d13200564b

    The solution is

    Disable all SQL jobs involved in replication on all the SQL server involved in Replication (publishers and subscribers)

    Run the below delete command(with your criteria) to clean the sysmergesubscriptions table. Execute this command on all the servers involved in replication (publishers and sunscribers)

    --Deleting Gency Mgt OFF and Contact & doc Mgt_OFF subscriptions @ New_Oil_Export

    delete from [New_Oil_Export].[dbo].[sysmergesubscriptions] where pubid = '956234F7-E73A-4A31-8A66-D56D36AFD60D' and subscriber_server = 'salsfafdba01'

    delete from [New_Oil_Export].[dbo].[sysmergesubscriptions] where pubid = 'E335ACED-8993-4E25-A517-511159013C8F' and subscriber_server = 'salsfafdba01'

    Enable all the SQL jobs again.

    This should finally DELETE your ghostly subscriptions!!!!

    PS: the above forum link that i have mentioned provides an easy way to disabling and enabling the SQL jobs on the server

    • Marked as answer by RubSay Monday, October 18, 2010 4:32 AM
    Monday, October 18, 2010 4:31 AM

All replies

  • Can you do this on your subscriber database sp_removedbreplication

    Then on your publisher drop the substription using sp_dropmergesubscription or sp_dropmergepullsubscription.

    Ensure the above procs remove the entry from sysmergesubscriptions on your publisher.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, October 12, 2010 12:26 PM
    Answerer
  • Thanks. But i dont think i can try what you are suggesting. It will remove all other replications (subscriptions) i have on the subscriber!
    Wednesday, October 13, 2010 10:25 AM
  • RubSay,

           I have same issue..did u find answer?? Plz let me know

    Sunday, October 17, 2010 2:01 PM
  • Yupp...i got an answer. The below forum helped me in my mysterious query on which i spent weeks for troubleshooting

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/df79fe81-9b5e-4726-88d9-d4d13200564b

    The solution is

    Disable all SQL jobs involved in replication on all the SQL server involved in Replication (publishers and subscribers)

    Run the below delete command(with your criteria) to clean the sysmergesubscriptions table. Execute this command on all the servers involved in replication (publishers and sunscribers)

    --Deleting Gency Mgt OFF and Contact & doc Mgt_OFF subscriptions @ New_Oil_Export

    delete from [New_Oil_Export].[dbo].[sysmergesubscriptions] where pubid = '956234F7-E73A-4A31-8A66-D56D36AFD60D' and subscriber_server = 'salsfafdba01'

    delete from [New_Oil_Export].[dbo].[sysmergesubscriptions] where pubid = 'E335ACED-8993-4E25-A517-511159013C8F' and subscriber_server = 'salsfafdba01'

    Enable all the SQL jobs again.

    This should finally DELETE your ghostly subscriptions!!!!

    PS: the above forum link that i have mentioned provides an easy way to disabling and enabling the SQL jobs on the server

    • Marked as answer by RubSay Monday, October 18, 2010 4:32 AM
    Monday, October 18, 2010 4:31 AM