Error 21074: Subscriptions being made inactive

Hi,
In my Single subscriber setup with Transactional Replication, i have
received this error on Distribution agent:
"The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated."
I know that this error occurs when the pull agent is unable to
transfer transactions to the Subscriber and the distribution DB is
piling up with transactions. And this happens for more than 48hrs
(default).
1. Can someone help in finding the possible reasons for my
distribution DB not running?
Am unable to see any errors, since all my log entries in the
Distribution Agent History have got cleaned because this issue occured
some one month back. All maintenance agents are run at default
settings.
2. As a solution, i just made the subscription status to Active by
running the below query:
update distribution..MSsubscriptions set status=2 where
publisher_id='x' and
publisher_db='x' and publication_id='x' and subscriber_id='x' and
subscriber_db='x'
However, the transactions which were run between subscription turning
from inactive to Active does not get synchronised. This causes some of
them to be lost. And only new transactions from the time it is made
active are being sent. Is this by design or changeable?
Please help me asap.
Thanks in advance,
Lavanya
On Apr 3, 12:07 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Have a look at the schedule of the distribution agent on the subscriber. If
> it's on demand then this is quite understandable and setting the transaction
> retention period, the history retention period and the publication's
> reinitialization period all to a suitably high value will prevent this
> occurring next time.
> At this stage you'll need to reinitialize rather than try to fix the status,
> as some commands might have been cleaned-up so there could be
> non-convergence occurring.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Thanks Paul.
Distribution agents run continuously from all publishers to this
centralized subscriber.
Due to some reason, the distribution agent was in retrying state for
more than the transaction retention period and hence the subscription
got marked as inacive.
Does History retention period cause subscription to become inactive?
Hope it does not.
Another clarification is whether setting transaction retention period
as 365 days, how do i measure/expect the transactional replication
performance to be affected? I use multiple publishers and a
centralised subscriber.
|||On Apr 3, 11:57 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> The History retention period needs to be set to be consistant with the
> transaction retention period.
> If the retention period is set to be 1 year, the distribution database can
> balloon up and the distribution agent can take progressively longer finding
> the commands to be sent to the subscriber.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
Let me know what do you mean by maintaining the history retention
period as consistent.
Can i set something like this? Will this have an impact on
performance?
History retention = 100 hours
Transaction retention = 72 hours.
Also, Does History retention period cause subscription to become
inactive?
Thanks,
Lavanya