Search code examples
sql-serverreplicationhigh-availabilitysql-agent-jobtransactional-replication

Log Reader fails: sp_replcmds procedure was called with the wrong number of parameters


After upgrading distributor to 2022 version, It turned out that for one distribution db replication doesn't work correctly and for second one everything is as it should be, Log readers fail with:

sp_replcmds procedure was called with the wrong number of parameters.

With -Outputverboselevel 3 for Log reader there're a lot of retries in job history:

2023-04-13 11:30:56.960 Publisher: {call sp_replcmds (500, 0, 0, , 0, 500000)}
2023-04-13 11:30:56.976 Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'PUBLISHER'.'.
2023-04-13 11:30:56.976 The process could not execute 'sp_replcmds' on 'PUBLISHER'.
2023-04-13 11:30:56.976 Status: 0, code: 18751, text: 'sp_replcmds procedure was called with the wrong number of parameters.'.

After running trace on Publisher it turned out that Log reader tries to run sp_replcmds with one additional parameter - listener name of publisher AG:

exec sp_replcmds 500,0,-1,0x,1000,0,500000, N'LISTENERNAME'

Solution was to redirect publisher to NULL, so distributor wouldn't have any recording about listener. But what if something will happen with primary Publisher? How to fix this issue and have replication with redirected publisher?


Solution

  • Just giving some additional insight here, this issue is a Bug within SQL Server 2022 that was ultimately fixed with CU5, released 2023-06-15. Applying this CU to my system fixed the issue immediately.

    Specifically KB2357336 is the KB.