?
Solved

Removing P2P node from SQL Server 2008

Posted on 2013-02-07
8
Medium Priority
?
652 Views
Last Modified: 2013-05-29
Hi,

I'm trying to remove a node from a SQL Server 2008 Peer-To-Peer replication (using the "Configure Peer-To-Peer Topology Wizard") and I'm receiving the following error after deleting the peer from the diagram:

The subscription does not exist and cannot be deleted.

I've already removed the publication and subscription from the node I'm trying to remove and from the main node but the node I need to delete still shows at the diagram.

How can I remove it?

Thank you!
0
Comment
Question by:BlitzkriegBR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:djbaum
ID: 38867309
Hi,

we had the same Problem, try this:
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks2008R2]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

Open in new window


source: http://msdn.microsoft.com/en-us/library/ms147833(v=sql.105).aspx
0
 

Author Comment

by:BlitzkriegBR
ID: 38867859
Hi djbaum,

I've executed the script you suggest on the node I need to remove from the replication and it returned this:

Msg 14013, Level 16, State 1, Procedure sp_MSrepl_droppublication, Line 87
This database is not enabled for publication.
The replication option 'publish' of database 'db_teste' has been set to false.

The node stills appearing at the P2P at the diagram...
0
 
LVL 5

Expert Comment

by:djbaum
ID: 38868049
This is bad, if I'm right the replication doesn't exist anymore, but the replication Monitor still displays it. If you want to remove it, you have to clear some tables manually like descriped here http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/1ae132ce-271a-4d51-a25b-b3cd4c28394a
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 5

Expert Comment

by:djbaum
ID: 38892153
You should do the following in Distribution database:

DELETE FROM MSpublications WHERE publisher_db = 'db_teste'
DELETE FROM MSpublisher_databases WHERE publisher_db = 'db_teste'
DELETE FROM MSarticles WHERE publisher_db = 'db_teste'
DELETE FROM MSlogreader_agents WHERE publisher_db = 'db_teste'
DELETE FROM MSrepl_originators WHERE dbname = 'db_teste'
DELETE FROM MSsnapshot_agents WHERE publisher_db = 'db_teste'
DELETE FROM MSdistribution_agents WHERE publisher_db = 'db_teste'
DELETE FROM msreplication_monitordata WHERE publisher_db = 'db_teste'
DELETE FROM MSsubscriptions WHERE publisher_db = 'db_teste'
DELETE FROM MSdistribution_agents WHERE publisher_db = 'db_teste'

Open in new window

0
 

Accepted Solution

by:
BlitzkriegBR earned 0 total points
ID: 39004026
Resoved it by removing all the replications from all the servers... Then configured them again...

It was a really pain in the @ss, but I didn't find any other way to solve this...

Now I got the exac same problem again!
0
 
LVL 5

Expert Comment

by:djbaum
ID: 39006146
The sript above did not help?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39195926
The other suggested methods didn't worked im this case.
So you are closing this question with a solution that did not work for you?  Would it not be more fitting to request that the question be deleted?

Let me know if you open a new question...
0
 

Author Closing Comment

by:BlitzkriegBR
ID: 39203966
The other suggested methods didn't worked im this case.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question