Solved

Removing P2P node from SQL Server 2008

Posted on 2013-02-07
8
578 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:djbaum
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Accepted Solution

by:
BlitzkriegBR earned 0 total points
Comment Utility
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
Comment Utility
The sript above did not help?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
The other suggested methods didn't worked im this case.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now