delete entries in replication monitor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

delete entries in replication monitor

Author

Topic

Garfield20001
Starting Member

4 Posts

Posted - 2007-10-02 : 13:45:04

Hi everybody,

when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used.

I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used.

The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone.

So, is there a way to solve my problem?

We are using sql 2005 server as publisher and are using merge replication.

Thanks for help in advanced

Garfield

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-02 : 13:55:00

When you right click on publisher and go to properties you will see a list of subscribers. You can delete from there.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Garfield20001
Starting Member

4 Posts

Posted - 2007-10-02 : 17:49:31

The problem is, that (maybe) I did a mistake in my programming.
When I look at the partitions of the publisher then I see only the different HOST_NAME()s. The column SUSER_SNAME is empty. In my special case there are several subscription with the same HOST_NAME. So I think, when I would delete a partition I would delete all subscriptions with the same HOST_NAME that means also the active subscription.
I think (hope) their must be a stored procedure to delete subscriptions manually from the database. But I can not figure out which one to use because all the sps needs a subscriptionId which I do not have or do not know where to get from.

Can you give me a hint?

René

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-02 : 18:11:31

check books online for sp_Dropsubscription and see if it helps.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-10-15 : 17:07:52

quote:
Originally posted by Garfield20001

...The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database.

Delete the data in MSreplication_monitordata and check.

------------------------
I think, therefore I am - Rene Descartes

Garfield20001
Starting Member

4 Posts

Posted - 2007-10-21 : 16:20:28

Thanks for your hints.

sp_dropsubscription didn't work (i don't know why).

the second hint (delete entries in MSreplication_monitordata) works as long as I don't start the replication-monitor. After the start all entries were restored. Here I don't know where these informations are additionally stored in the sql-server.

Do you have another idea? What kind of information do you need, so that you can help me?

Thanks

René

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-21 : 17:15:43

Tried right click on those subscribers and choose delete in replication monitor?

Garfield20001
Starting Member

4 Posts

Posted - 2007-10-22 : 15:57:30

Yes, I did. But unfortunatly no context menu appears. What I think about is that there is a bug in the replication monitor.
Additionally there is another problem. Once I installed a publication and from that publication some anonymous pull-subscriber. I opened the repl-monitor. The publisher and its subscribers appeared. Afterwards I deleted the publication using the sql management studio. But still this publication although no longer existing appears in the repl-monitor and there is no way to delete it.
What about the distribution database? If I delete this once, I'm sure that everything is clean. But the question is: Do the subscriber still replicate with the publisher?

Thanks for response

René

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2007-10-22 : 16:08:20

quote:
Originally posted by Garfield20001
What about the distribution database? If I delete this once, I'm sure that everything is clean. But the question is: Do the subscriber still replicate with the publisher?

If you delete distribution DB, you need to create new replication.
There is a system tables for subscriber, delete the entries in the table.

------------------------
I think, therefore I am - Rene Descartes

landyman
Starting Member

1 Post

Posted - 2008-11-25 : 12:28:28

quote:
Originally posted by Garfield20001

Hi everybody,

when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used.

I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used.

The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone.

So, is there a way to solve my problem?

We are using sql 2005 server as publisher and are using merge replication.

Thanks for help in advanced

Garfield

I had a similar problem with a Peer to Peer subscription that was no longer required.
exec [distribution].sys.sp_replmonitorhelppublication @publisher = N'', @refreshpolicy = N'0' (called by Replication Monitor) repopulates dbo.MSreplication_monitordata. You need to check no data associated with the subscription dbs from the following tables....
delete distribution.dbo.MSsubscriptions where
delete distribution.dbo.MSdistribution_agents where
delete distribution.dbo.MSpublication_access where
delete distribution.dbo.MSpublisher_databases where
delete distribution.dbo.MSrepl_originators where
delete from distribution.dbo.MSsnapshot_agents where
delete from distribution.dbo.MSsnapshot_history where
delete from distribution.dbo.MSsubscriber_info where
delete from distribution.dbo.MSsubscriber_schedule where

calls to sys.sp_replmonitorhelppublication should not show the removed publications\subscriptions.

Interestingly [sys].[sp_replmonitorhelppublication] gets refresh data from ....
sys.sp_replmonitorrefreshdata which is undocumented (can't find it in any System DB either)....

CheveriSQL
Starting Member

1 Post

Posted - 2011-12-20 : 05:37:22

Old Publications in replication monitor was not cleared until I delete the entries from [distribution].[dbo].[MSsnapshot_agents].

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources