Is there a way to setup one Alert for all Replication errors?

Question

I have a SQL Server 2016 (Standard) instance, with DatabaseA, who is the Publisher in a Transactional Replication (push subscription) topology. A second SQL Server 2016 (Standard) instance, with DatabaseB, is the Subscriber in this topology. The Distributor lives on the Publisher's server.

Recently I've encountered a series of different kinds of replication errors I've eventually realized were occurring, and easily resolved, but had no immediate awareness of when they occurred. One example error was "20598: The row was not found at the Subscriber when applying the replicated command." Again, it was easily solvable once I was aware it was occurring and that replication was down, but I wasn't aware this issue was happening for about a day.

I know SQL Server Agent Alerts can be setup to alert on specific errors, but how would I know which errors I should setup alerts for? (Microsoft reserves the first ~50,000 errors codes, and it appears there could be hundreds that could pertain to replication.)

Is there a broader way to setup an alert for all replication based errors? Or is there a better way to monitor and be alerted when replication is down?

Currently, my workaround was to create a single alert for Severity Level 16 which contains the message text "repl" in hopes I only catch replication related errors at that severity level.

Answer 1

If you want all replication errors, you're better off rolling something that monitors the MSrepl_errors table.

MSrepl_errors is located in the distribution database, and will have all errors from the distribution and merge agents.

Since it wouldn't be based on Perfmon counters, error numbers, etc, you wouldn't be using the Agent Alert mechanism, but could fairly easily create a job that runs regularly, polls that table, and emails when new rows are logged.