This mystery often starts with the land of Replication Montor being very unhappy, providing a lot of red errors.

Unhappy-land in Replication Monitor can in this instance be relatively easily fixed.

If you manage to keep your cool and look deeper into the errors, you would probably see something like this:

Could not find stored procedure 'sp_MSdel_SalesCustomerTransactions'. (Source: MSSQLServer, Error number: 2812)

Something has happened with the procedures in the replicated database. It is not unlikely that someone with a bit too much access has done some tidying, or maybe a deployment has cleaned out the procedures needed.

Luckily it is not too hard to fix once you know what to do:

USE [WideWorldImporters];
EXEC sp_scriptpublicationcustomprocs @publication='Replication';

The procedure sp_scriptpublicationcustomprocs should be run in context of the database you have issues with (so not the replicated database) and you need to specify the publication name.

You will then be presented with the wonderful list of all the procedures that should exist in your replica database. If you know which are missing, you can go ahead and execute those, and if you don’t have a clue, you could run the whole script. Note that if you ever made any changes to the procedures, you need to remember what those changes was and apply those again, as the procedure will create the default replication procedures.

How does the sp_scriptpublicationcustomprocs work? In case you are curious, you can look at the blog from Percy Reyes to find more information (linked below).

It reads the replication metadata from your publication and executes inside another replication stored procedures such as sp_scriptinsproc, sp_scriptdelproc (or sp_scriptxdelproc) and sp_scriptupdproc (or sp_scriptxupdproc) for each article in order to generate the INSERT, UPDATE and DELETE procedures.

Percy Reyes