Maximum Retention period of the distributor Database (transactional replication)does not work

Hi,

I tested the Maximum Retention period of the distributor Database but my test
failed and I would like to know why?
Here after the scenario that I performed:

I set up a transactional replication with a pull subscription.
I set Maximum Retention period of the distributor Database to 1hr (the default
is 96hrs).
I run a process that populate my database and I disconnect the
subscriber server from the network.
It works fine during the first hour (replication status: retry)
Then the replication status was in error telling (after one hour):

"The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be dropped and
recreated. (Source: MSSQLServer, Error number: 21074)
Get help: http://help/21074"

If I reinitialize the subscription using the current snapshot (generated
before the test) I got the error: "The initial snapshot for publication
'Pub_G2U_Txn_Push' is not yet available."
I have to reinitialize the subscription by generating a new snapshot. In
this case, of course all records are replicated even those that should
be dropped due to the retention period set.

I did this test several times and each time I got the same problem
(replication error when the Maximum Retention period is reached)?

Is there any explanation?
Did I miss something?

Regards
Emmanuel

Hi Emmanuel,

From BOL:

------------------------------------------------------------------------
Transactional replication uses the maximum distribution retention period
(the @max_distretention parameter of sp_adddistributiondb (Transact-SQL)) and
the publication retention period (the @retention parameter of
sp_addpublication (Transact-SQL)):

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.

If a subscription is not synchronized within the publication retention
period (default of 336 hours), the subscription will expire and be dropped by
the Expired subscription clean up job that runs on the Publisher. The
subscription must be recreated and synchronized.
------------------

Rubén Garrigós
Solid Quality Mentors


"ezc" wrote:

> Hi,
>
> I tested the Maximum Retention period of the distributor Database but my test
> failed and I would like to know why?
> Here after the scenario that I performed:
>
> I set up a transactional replication with a pull subscription.
> I set Maximum Retention period of the distributor Database to 1hr (the default
> is 96hrs).
> I run a process that populate my database and I disconnect the
> subscriber server from the network.
> It works fine during the first hour (replication status: retry)
> Then the replication status was in error telling (after one hour):
>
> "The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be dropped and
> recreated. (Source: MSSQLServer, Error number: 21074)
> Get help: http://help/21074"
>
> If I reinitialize the subscription using the current snapshot (generated
> before the test) I got the error: "The initial snapshot for publication
> 'Pub_G2U_Txn_Push' is not yet available."
> I have to reinitialize the subscription by generating a new snapshot. In
> this case, of course all records are replicated even those that should
> be dropped due to the retention period set.
>
> I did this test several times and each time I got the same problem
> (replication error when the Maximum Retention period is reached)?
>
> Is there any explanation?
> Did I miss something?
>
> Regards
> Emmanuel
>
>
>