SQL Server Log Reader Agent while trying to add node in p2p Replication

Question

I am trying to add one more node in peer-to-peer cluster. In current state, I have 3 servers A, B and C. I need to add a node D with node A(only). For this, I used following steps:

• Took full backup from Node A
• Copied it to Node D
• Restored the backup
• Added publications in both nodes.
• Added subscription for Node D in publisher Node A with sync_type 'initialize with backup'

As soon as the commands were run, the Log Reader in Node A started throwing the below errors:

• The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007) Get help: http://help/1007
• The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007) Get help: http://help/1007
• IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000) Get help: http://help/MSSQL_REPL2000
• The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037

As this was affecting the p2p replication between Nodes A, B and C, I removed the subscription for Node D. This didn't resolve the error. On further investigation I found that the Distribution Cleanup job was still running. Probably that caused the issue.

Now, I am just hoping to make the p2p replication between already existing nodes A, B and C working again, the way it was before. But, all my attempts have resulted in no change in Log Reader Agent. It is stuck at the same error. - The process could not execute 'sp_MSadd_replcmds' on 'Node A'. (Source: MSSQLServer, Error number: 1007) Get help: http://help/1007 - The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup. (Source: MSSQLServer, Error number: 1007) Get help: http://help/1007 - IDistPut Interface has been shut down. (Source: MSSQL_REPL, Error number: MSSQL_REPL2000) Get help: http://help/MSSQL_REPL2000 - The last step did not log any message! (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037

Please advise on any steps to resolve this. Thanks

Answer 1

Adding the resolution for the benefit of anyone who faced this issue on SQL 2012:

We engaged MS support on this. Apparently their stored procedure "sp_dropsubscriber" misses some tables sometimes. In our case, this entry was not deleted from table MSnosyncsubsetup. Once they found this out after few hours of analysis, a backup was taken and the row related to new subscriber was deleted. This fixed the log reader and replication hummed along properly.