SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ShekharNaidu
ShekharNaidu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 70

Thank you for replies...

I will try those suggestions..

Shekhar




Thanks ,

Shekhar

ShekharNaidu
ShekharNaidu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 70

Andrew,

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

Thank You

Chandra Shekar




Thanks ,

Shekhar

Brad Scheepers
Brad Scheepers
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 279
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.
Wired-528862
Wired-528862
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 56
Brad Scheepers (9/18/2008)
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.



Can you expand on this more. I'm new to replication and I'm not sure where to check the Snapshot agent schedule. Thanks.
sunrise.freedom
sunrise.freedom
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 15
Hi guy,
just run this query in a new query window in the distribution server :-)

USE distribution

Update [MSsubscriptions]
Set [status] = 2


Wired-528862
Wired-528862
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 56
^^^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.
dsusca
dsusca
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 56
Tanks for this script :-)
bbalub
bbalub
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 212
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
sqldba.avinash
sqldba.avinash
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 1
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 = Inactive
1 = Subscribed
2 = Active


This steps wil give you the solution to above problem, i fixed it through the following steps only


Avinash
Barry Jones-426846
Barry Jones-426846
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 56
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 MSsubscription

4. 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search