SQLServerCentral / SQL Server 7,2000 / Replication / The subscription(s) have been marked inactive..... / Latest Posts

RE: The subscription(s) have been marked inactive.....

MudLuck — Thu, 27 Dec 2012 10:14:12 GMT

This sets your article back to active. :w00t:

RE: The subscription(s) have been marked inactive.....

Barry Jones-426846 — Fri, 15 Jun 2012 12:16:26 GMT

If you don't watch the errors (or the alert fails in our case) for a few days then the subscription will be marked inactive.Issues with the login account used will prevent a subscription from being active. It will simply fail to log on.Also, if it has been inactive for a while then the subscriber will be out of sync - as in the case with updatable transactional subscriptions.Finding the cause of the inactivity is best viewed from the Job history, not replication monitor (which says it is inactive, not why). Find the job, check the history out to identify the cause.Resolution steps...1. Stop the job. (or all replication jobs for the database (including from other publications eg 2 way upd...trans...repln))2. Apply fix: Change the subscription properties to use a different account (if this was the cause).3. Apply workaround: Use the preferred update script or sp mentioned before to set the status = 2 (applies to each articles in the publication) in MSsubscription4. Restart the job. Watch the Job Activity as well to make sure it is running OK. If not, find the cause, repeat steps.If you are still getting a failure caused by undistributed transactions failing to get through then you may have to clear the MSrepln_commands and manually sync changes using RedGate SQLCompare before restarting the job.Simply reinitialising a subscription isn't always practical when the publication database is over 20Gb, the subscriber has a crap connection on the other side of the world and has only a small maintenance window to get the job done.Good luck.

RE: The subscription(s) have been marked inactive.....

sqldba.avinash — Tue, 11 Oct 2011 11:49:10 GMT

Whenever there is an error as mentioned above then, you can try to update the status column in the MSsubscriptions table in the distribution database. The status column for the expired subscription indicated a value of 0 meaning inactive. The value of 2 in the status column means Active.Try the following steps:1. Select * from MSsubscriptions to locate the expired subscription.2. Use the query below to reset the status in MSsubscriptions table. Fill in the values for the publisher_id, publisher_db, publication_id, subscriber_id and subscriber_db in the query below with the values from the expired subscription in the MSsubscriptions table.update distribution..MSsubscriptions set status=2 where publisher_id='x' andpublisher_db='x' and publication_id='x' and subscriber_id='x' and subscriber_db='x'Status of the subscription:0 = Inactive1 = Subscribed2 = ActiveThis steps wil give you the solution to above problem, i fixed it through the following steps onlyAvinash

RE: The subscription(s) have been marked inactive.....

bbalub — Wed, 24 Nov 2010 03:47:28 GMT

Threre are many reasons when a subscriptions becomes inactive. I too got into similar situation. Microsoft says "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 configure this retention period through right click Replication -> Configure Distributor -> Retention Period

RE: The subscription(s) have been marked inactive.....

dsusca — Mon, 26 Jul 2010 11:34:36 GMT

Tanks for this script :-)

RE: The subscription(s) have been marked inactive.....

Wired-528862 — Tue, 20 Oct 2009 14:09:50 GMT

^^^Thanks for the script.FYI- I did more research and found that it appears the subscriptions will be marked inactive if nothing is replicated in 48 hours. To resolve this, I have an update script running every 12 hours that updates a users lock status to unlocked or something similar. Just update something that's replicated and so far I have not had any subscriptions marked as inactive for a few weeks now. Hope this helps.

RE: The subscription(s) have been marked inactive.....

sunrise.freedom — Tue, 20 Oct 2009 14:04:45 GMT

Hi guy, just run this query in a new query window in the distribution server :-)[code="sql"]USE distributionUpdate [MSsubscriptions]Set [status] = 2[/code]

RE: The subscription(s) have been marked inactive.....

Wired-528862 — Fri, 08 May 2009 16:27:46 GMT

[quote][b]Brad Scheepers (9/18/2008)[/b][hr]Hi guys ...When it says the snapshot has been marked as inactive, it's because the snapshot needs to run on it's own schedule in the background (and seems to ignore that it is marked as never expiring).So check the schedule of the snapshot agent and let it run something like once a week on a Sunday morning for example. You may still have to reinitialise the publication to get it all sorted out again.[/quote]Can you expand on this more. I'm new to replication and I'm not sure where to check the Snapshot agent schedule. Thanks.

RE: The subscription(s) have been marked inactive.....

Brad Scheepers — Thu, 18 Sep 2008 02:06:03 GMT

Hi guys ...When it says the snapshot has been marked as inactive, it's because the snapshot needs to run on it's own schedule in the background (and seems to ignore that it is marked as never expiring).So check the schedule of the snapshot agent and let it run something like once a week on a Sunday morning for example. You may still have to reinitialise the publication to get it all sorted out again.

RE: The subscription(s) have been marked inactive.....

ShekharNaidu — Tue, 26 Sep 2006 01:17:00 GMT

Andrew,

Can you please tell me the job name which is monitoring the subscribers expiration..

Thank You

Chandra Shekar

RE: The subscription(s) have been marked inactive.....

ShekharNaidu — Mon, 26 Jun 2006 02:10:00 GMT

Thank you for replies...

I will try those suggestions..

Shekhar

RE: The subscription(s) have been marked inactive.....

Borik — Fri, 23 Jun 2006 15:24:00 GMT

Also u might want to try to increase number of retries for replication jobs...

RE: The subscription(s) have been marked inactive.....

Dinakar Nethi-176633 — Fri, 23 Jun 2006 15:08:00 GMT

If you already have the error, the only solution I would think is to re-setup replication but make sure you check the "never expires" option as Borik mentioned. By default I think its around 336 hours of inactivity. For a production system it is rare to have such period of inactivity.

RE: The subscription(s) have been marked inactive.....

Andy Warren — Fri, 23 Jun 2006 07:33:00 GMT

I dont have a server with replication enabled with me at the moment, but there is a job that checks for expired pubs by looking at the date of the oldest open (undistributed) transaction and marks sub as expired if past x date. Try disabling the job. Though I suspect that may be bandaiding a bigger problem, probably worth digging more.

RE: The subscription(s) have been marked inactive.....

ShekharNaidu — Fri, 23 Jun 2006 05:44:00 GMT

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

I am getting this error frequently .. and it requires me to go through all the process of creating subscriptions again and again...

i would be happy if anybody help in this regard.

I have "Subscription Expiration perios set to 336 hrs" ... even if it is set to 336 hrs i am getting the above error frequently i.e., bfore this expiration period.

Thank you

Shekhar

RE: The subscription(s) have been marked inactive.....

Todd Boothroyd — Mon, 26 Sep 2005 14:07:00 GMT

Hey, not sure all of the steps that I took to resolve my issue, but here's what I recall:

Under Replication Monitor at the Distributer, expand node.

Expand Publishers

Expland node next to server name (Blue-green book icon)

Right click on any of the publishers and choose Reinitialize all subscriptions. (alternately, you can choose properties and select reinitialize under the Subscriptions tab). This just marks the subscription to be reinitialized.

Next, on Status tab, I clicked Run Agent Now to trigger the snapshot; this is supposed to, in my setup, push the subscription but that didn't happen.

Closing the properties window (if open), I selected the publisher in question so the information appeared in the right pane. Selecting the Publish Folder with the red X, I right clicked and chose Start Synchronizing.

That did it for me; hopefully, I chose the right setting in properties so it doesn't happen again.

Cheers!

RE: The subscription(s) have been marked inactive.....

Borik — Tue, 01 Mar 2005 07:34:00 GMT

Enterprise Manager ->Replication Monitor ->Publishers ->ServerName ->Right Click on Publisher ->General Tab -> at the bottom there is Radio button "Subscription never expires..."P.S. Unfortenetly i did't find how to resolve the error, i think u will neet to redo Replication....Hope it helps... Borik

RE: The subscription(s) have been marked inactive.....

togi — Tue, 01 Mar 2005 02:44:00 GMT

Hi All

I also got same error..I looked to the some forums on net and everybody say that its related with expiration time..its default 72 hours and it can be set longer.. What is that max value of expiration time?Actuallly I want to set my replication as never expired..I mean it should be never expired..How can I set expire time as "unlimited"..

Thanks ..

RE: The subscription(s) have been marked inactive.....

Borik — Mon, 14 Feb 2005 08:51:00 GMT

Umm i just got this error "The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated." any Help to get this working again would be appreciated...thanks...

RE: The subscription(s) have been marked inactive.....

magasvs — Tue, 11 Jan 2005 11:43:00 GMT

Some replication parameters require reinitialization of subscriptions. So, if you change something in replication make sure this settings do not require reinitialization.

The subscription(s) have been marked inactive.....

jmittmann — Tue, 11 Jan 2005 05:03:00 GMT

Hallo,

from time to time my transactional (push) replications on SQL2000 SP3a servers send me the following error message:

Error Message : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.Error Detail : The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: Server Name (Data source); Error number: 21074)

My subscriptions have been very active for at least 2 years and in the properties "Subscriptions never expire" is marked.

I know that I can restart replication by setting the status column in distribution..MSsubscriptions to 2 but can anyone tell me why this problem occurs and how I can prevent it in future?

Thank you for your help