Error in CDC Capture job, "Could not find stored procedure 'sys.sp_ins_dummy_lsn_time_mapping_with_replnoop' "

Question

My team and I have CDC enabled on a table that doesn't frequently receive updates. Each week the table could receive around 5k new rows. We're currently running SQL 2022 Enterprise Edition. Previously SQL 2022 was on RTM and the CDC Capture job worked fine. As of 3 weeks ago SQL was patched to CU13 and now we're receiving the error,

"Msg 2812, Level 16, State 62, Could not find stored procedure 'sys.sp_ins_dummy_lsn_time_mapping_with_replnoop'. For more information, query the sys.dm_cdc_errors dynamic management view."

I've only found one post online using a Google search and the individuals is being redirected because they asked the question in the Spanish forum.

I'm assuming that the job is erroring due to how frequently the data is updated. The database & table are required for CDC as it is needed per a business requirement. We tried disabling and enabling CDC at the database level to reinitialize CDC, but the error continued. As a work around we've added a 5 min schedule to the cdc.db####_capture job.

Is this potentially a bug or has anyone else ran into this issue?

• The job is setup by default to retry 10 times before it errors out.
• The pollinginterval was set to 5 seconds. I've tried 30 seconds to 300 seconds (five mins) and the error continues.
• I can see in the job logs where transactions are being extracted

Answer 1

Hi @Godwin, Colby,

Thank you for your reaching out and welcome to Microsoft Q&A!

When one of the transactional INSERT, UPDATE, and DELETE stored procedures at the subscriber is dropped, then the error "Could not find stored procedure sp_MS%" will be raised.

In order to fix this error, we will need to recover the missing procedure and deploy it to the subscribers. We have two options to rebuild a large number of SQL Server Replication Stored Procedures:

1. You can reinitialize the subscriptions to recreate new INSERT, UPDATE, and DELETE stored procedures on the subscription side. To be honest, I do not recommend this option because it may be a resource intensive task and not productive especially for big databases or tables.  Reinitializing can also this block other users during the synchronization process.
2. You can use sp_scriptpublicationcustomprocs system stored procedure to recreate the creation code of the INSERT, UPDATE, and DELETE stored procedures for one particular publication. This option is a bit more feasible and what I generally recommend.

NOTE: You should always save all these system replications stored procedures in a deployment folder with your ticket or in your source control system.

In addition, I got this method from one thread:

I hope this helps! If you have any more issues or need further assistance, feel free to share with us!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

I don't know the full details here, but on Patch Tuesday there was a GDR for SQL Server, and the text said that the purpose of the GDR was repair things with CDC that broke with an earlier GDR.

Take a look at https://support.microsoft.com/en-us/topic/kb5046059-description-of-the-security-update-for-sql-server-2022-cu15-october-8-2024-b592d86f-3351-4f9f-9c80-ef495a0137c1 and the article it links to. The symptoms describe does not really match the problem you describe, but it is worth checking out anyway.