0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.