Vijay RodriguesMicrosoft SQL Server Support
SQL Server Management Studio provides a way to monitor events, such as replication agent events, using alerts. SQL Server includes a set of predefined alerts for replication agents. If you would like to start the monitoring for replication agent failures, please follow below steps. The below is configured on the distribution instance (for both push or pull subscriptions). The below example is for Transactional Replication.
1. In SQL Server Management Studio, expand SQL Server Agent and then expand Alerts
2. Right click ‘Replication: agent failure’ and click on Properties.
3. Note in the above screen shot that this alert is not enabled by default. Check the box to Enable the alert then click OK (no other changes necessary on this screen unless you would like to customize). The type “SQL Server event alert��� tell SQL Server to log the message in the Microsoft Windows event log.
4. You should now see the alert enabled (without the red arrow) in SQL Server Management Studio indicating SQL Server will write next agent failure to event log
About SCOM monitoring for SQL replication: If the only requirement is to monitor replication alert, you don’t need the SQL management pack (which is available in SCOM catalog). The replication alert mentioned can be monitored using custom SCOM's management pack. Using SCOM and below email, we can get an alert generated for this event recorded in the event log.
Here is the TechNet link to create a Unit monitor to check when an event raised in the Windows Application Event log.
About replication agent message IDs: The above section mentions how to enable alerts for replication agent failures. The message ID for such a failure is 14151.
Condition causing the alert to fire
Enters additional information in sysreplicationalerts
Replication: agent success
Agent shuts down successfully.
Replication: agent failure
Agent shuts down with an error.
Replication: agent retry
Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).
Replication: expired subscription dropped
Expired subscription was dropped.
Replication: Subscription reinitialized after validation failure
Response job 'Reinitialize subscriptions on data validation failure' reinitializes a subscription successfully.
Replication: Subscriber has failed data validation
Distribution or Merge Agent fails data validation.
Replication: Subscriber has passed data validation
Distribution or Merge Agent passes data validation.
Replication: agent custom shutdown
Using Alerts for Replication Agent Eventshttp://msdn.microsoft.com/en-us/library/ms151752(SQL.90).aspx
How to: Configure Predefined Replication Alerts (SQL Server Management Studio)http://msdn.microsoft.com/en-us/library/ms152496(v=SQL.90).aspx