Welcome to SqlServerFaq.net

We have observed a number of case where we use sql server replication on a clustered instance of sql server and when we try to failover from Node 1 to Node 2 the Log Reader agent may fail to start with the following error

Error messages:

The process could not execute ’sp_MSpub_adjust_identity’ on ‘CRPSCSMSQ69V1\PUB’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: <<http://help/MSSQL_REPL20011>>

An error occurred during decryption. (Source: MSSQLServer, Error number: 15466)

Get help: <<http://help/15466>>

The process could not execute ’sp_MSpub_adjust_identity’ on ‘CRPSCSMSQ69V1\PUB’. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: <<http://help/MSSQL_REPL22037>>

As seen above the root cause of the issue is the error 

“An error occurred during decryption”

Which mean when the sql server instance failover to Node 2 it is not able to decrypt and run the stored procedure sp_MSpub_adjust_identity and hence it is not able to execute the stored procedure sp_MSpub_adjust_identity which is causing he Log Reader to fail on Node 2

It was not able to decrypt the stored procedure the Service Master Key on Node 2 was different then the Service Master Key on Node 1 . So one of the way to resolve the above error is to backup the service master key from Node 1 and restore the same on Node 2

BACKUP SERVICE MASTER KEY TO FILE = ‘path_to_file’ ENCRYPTION BY PASSWORD = ‘password’

RESTORE SERVICE MASTER KEY FROM FILE = ‘path_to_file’
DECRYPTION BY PASSWORD = ‘password’ [FORCE]

 Another resolution can be to regenerate the service master key on Node 2 with Force option but in case when you fail back to Node 1 the Log Reager agent will fail again with the same error.

ALTER SERVICE MASTER KEY REGENERATE FORCE

However we regenerate the Service Master Key the Linked servers on the server can fail and we may have recreate the Linked servers or change the password for the Linked servers which can tedious and undesirable

The cause of the above issue occurs when we change the service account of the clustered instance of sql server using the services console. For a clustered instance of the sql server we recommend to change the service account using the sql server configuration manager because it performs a number of operation in the background which takes care of the service master key when you failover to Node 2.

So to resolve the above error we need to change the Service Account to another account using the Configuration Manager on Node 1 and try to failover on Node 2 and check whether the Log Reader Agent worked fine.

We can then change the Service Account back to the original account using the Configuration manager and try to failover again and the replication should work fine.

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