The subscription fails to Sync with the Publisher and marked inactive even though the settings have been configured to “never expire”

  • Article

We get the below error message when distribution agent tries to sync,

Error: 14151, Severity: 18, State: 1.
The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

CAUSE:
======
When we check the Publication properties, we find that the options for subscription expiration are marked as "Subscriptions never expire".

When we check the sql server error log, we see that the following error is reported in the error log,

Message in Errorlog :-
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 scheduled for retry. Query timeout expired
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

From the above error log it is clear that the Subscriber was deactivated due to some reason.

So in order to find the cause of deactivation we need to check the Transaction Retention period in the Distribution properties. If it is set to a lower number like 3 hours, then this indicates that if the subscriber does not synchronies in 3 hours, the distribution cleanup agent will truncate the commands tables in distribution and hence the subscriber will be marked as deactivated.

Since the commands are deleted from distributor there is no other option but to reinitialize the subscriber.

Also to find the cause of the Distributor not being able to sync we can check the Distribution history in the below tables on distribution database,

msdistribution_agents
msdistribution_history

Watch out this space for more interesting replications facts...

Parikshit Savjani
SE, Microsoft SQL Server