How to monitor the Health of SQL Server Replication

http://blogs.msdn.com/b/repltalk/archive/2010/09/20/how-to-monitor-the-health-of-sql-server-replication.aspx

Chris Skorlinski 
Microsoft SQL Server Escalation Services

Today someone asked me how to “roll their own” SQL Replication Monitor to keep an eye on the health of SQL Server Replication.  You’ve seen from my previous post I usually use a TracerToken job to monitor SQL Transactional Replication and query against MSmerge_sessions table for Merge Replication.  However, you can also create your own “Replication Health Check” stored procedures which execute the same queries used by SQL Replication Monitor.

The SQL Books Online covered these in great detail.  Search on INDEX for these stored procedures.  The BOL topic include syntax and output.

• sp_replmonitorhelppublication (http://msdn.microsoft.com/en-us/library/ms186304.aspx)
• sp_replmonitorhelppublisher
• sp_replmonitorhelpsubscription(http://msdn.microsoft.com/en-us/library/ms188073.aspx)
• sp_replmonitorsubscriptionpendingcmds 

Or use the links below as a starting point to read more about writing your own code..

How to: Programmatically Monitor Replication (Replication Transact-SQL Programming) 
-- and for the coders -- 
How to: Programmatically Monitor Replication (RMO Programming)

If you have sample code or links to related topic, COMMENT this blog posting and I’ll your information on this blog.

Scripts: (from envykok)

sp_replmonitorhelppublication @publisher='servername'

 

sp_replmonitorhelppublisher @publisher='servername'

 

sp_replmonitorhelpsubscription @publisher='servername',@publication_type='0'

 

sp_replmonitorsubscriptionpendingcmds @publisher='servername',

 @publisher_db='adventureworks',

 @publication='adventureworks_repl',

 @subscriber='servername',

 @subscriber_db='adventureworksrepl',

 @subscription_type='0'