Solved

Removing P2P node from SQL Server 2008

Posted on 2013-02-07
8
595 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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