[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Drop and Re-create Subscription on a server(P2P Replication)

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-06-21
I have a peer to peer transitional replication running on two servers. All of a sudden the subscriber on one of the servers went down with the following error.
How do I drop and recreate a subscription in MSSQL 2008?
Question by:isaackhazi
LVL 13

Expert Comment

by:Rahul Agarwal
ID: 35464588
To Drop Subscription on a server(P2P Replication) refer the link:


To Re-create Subscription on a server(P2P Replication) refer the link:

LVL 21

Expert Comment

by:Alpesh Patel
ID: 35465018
Using Context menu delete the subscription and create using wizard.

Author Comment

ID: 35465327
I cannot do that as it is a P2P trans replication.

I deleted it using the context menu but Im unable to add the subscription back...

So then I tried to add it thru a SP ..

use [PRTR_recruit_live]
exec sp_addsubscription @publication = N'PublicationPRTR', @subscriber = N'prtrescrmserver', @destination_db = N'PRTR_recruit_live', @sync_type = N'initialize from lsn'

with the above I get an error saying @subscriptionlsn cannot be NULL...

Any ideas...

I seriously dont want to re-do the entire replication again. as the other server is at an off site location about 200 kms from here and the database size is 40 GB

Accepted Solution

isaackhazi earned 0 total points
ID: 35465546
I figured it out.

set @sync_type='replication support only' and @status = N'active'
use [PRTR_recruit_live]
exec sp_addsubscription @publication = N'PublicationPRTR', @subscriber = N'prtrescrmserver', @destination_db = N'PRTR_recruit_live',@sync_type='replication support only', @status = N'active'

Open in new window

LVL 71

Expert Comment

ID: 36032524
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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