Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Removing P2P node from SQL Server 2008

Posted on 2013-02-07
8
Medium Priority
?
677 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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