Sql-server – SQL Server Log Reader Agent while trying to add node in p2p Replication

replicationsql server

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

Best Answer

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.

Related Question