Apr
4
2012

SQL Server 2000 – The subscription(s) have been marked inactive and must be reinitialized

Error: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

 

Environment: SQL Server 2000 SP4 (valid for other SP versions as well)

 

Replication Type: Transactional Replication with Publisher and Distributor on one server and Subscriber on another

Scenario: You have created subscription to non-expire subscription as below,

#img1#

And still from time to time you see the subscriber marked inactive error and your replication being broken; you should be amazed at what went wrong!

Solution: Now go through below steps and you will understand what went wrong and how to fix it,

As per Microsoft, “If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.”

This means that if you have some changes in the distributor that has to be propagated to the Subscriber and if that retention period exceeds the maximum retention period 72 hours by default, then the job Distribution clean-up will make your subscriptions inactive. You can reconfigure your distributor database settings as follows,

  1. Go to Enterprise Manager > SQL Server > Right Click Replication > Select “Configure Publishing, Subscribers, and Distribution…”

#img2#

  1. Now go to Section databases, select your distributor database and click ‘Properties’

#img3#

  1. Default setting would look like below fig., it will show Transaction retention period as 72 hours and history retention as 48 hours,

#img4#

  1. Modify ‘Transaction retention’ period to the specific duration you would like, here it is set to 365 days (a year), but keep in mind that it will over grow distribution database size if replication kept failed for longer duration,

#img5#

  1. Click Apply (Optionally you can update History retention as well)

Now that retention period is fixed, so there are less chances that subscription will expired unless you fix it before your newly set period like for above it is 365 days.

But currently replication is broken, so fix it by following any of below three methods,

Method 1 (Reinitialize Replication by standard method): Plain and Simple, reinitialize replication by going to subscription, right click and mark ‘Reinitialized Subscription’ and you can opt for generate Snapshot now or later whatever suitable. If your data is small then you can generate snapshot and sync data but in case of large data you need appropriate downtime on subscriber and performance hit on publisher.

Method 2 (temporary method to keep replication running for some more duration): Now if you need to keep running replication for mean time then you can modify system tables where we will alter replication status but it may or may not work, you can match the records or run validation to check if everything is fine. This method is at your own discretion and can be opted until you get downtime to apply Method 1. For this, execute below steps,

  1. execute Select * from MSsubscriptions to locate the expired subscription.

Status of the subscription:

0 = Inactive

1 = Subscribed

2 = Active

  1. Use the query below to reset the status in MSsubscriptions table. Fill in the values for,

publisher_id

publisher_db

publication_id

subscriber_id

subscriber_db

in the query below with the values from the expired subscription in the MSsubscriptions table.

In case there is only one publication, then execute following query,

As soon as this is done, start distribution agent and your replication should start fine and if you see error 20598, then do following,

go to Distribution Agent > Go to Properties > Go to Steps > Select ‘Run agent’ step > click Edit > go to command > in the end of command insert following > -Continuous –skiperrors 20598

Hopefully it will keep running until you get appropriate time to fix replication by Method 1 or 3.

Method 3 (Advance method for restoring replication using backup and restore):  It is an advanced method using sync with backup option and very well described by Microsoft at following link; you should have sync with backup enabled to use this method. Read more about this method on the link provided above.

My other articles on replication:

My article (link) describe timeout issues and some good practices for configuring replication and article (link) provides a replication monitoring script which can be deployed to identify issue proactively.

Other useful references:

http://www.sqlservercentral.com/Forums/Topic154744-7-2.aspx

http://msdn.microsoft.com/en-us/library/aa237094(v=sql.80).aspx (Strategies for Backing Up and Restoring Transactional Replication)

http://msdn.microsoft.com/en-us/library/aa178831(v=sql.80).aspx (Validating Replicated Data)

http://www.replicationanswers.com/General.asp

http://www.replicationanswers.com/Transactional.asp

http://www.mssqltips.com/sql-server-tip-category/7/replication/ (Several Replication Tips)

 

 

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: