How to monitor replication subscription errors

 One of my client has a big setup Server for SQL Server. He also has a lot of transactional replication and monitoring its is a really problem for him. Frequently he has problem of out of sync replications or failed replication

Also sometimes if devs stop synchronizing replication and forget to start it. He need an alert for this. During this we setup number of processes to monitor this. I would like to share one of store proc for this which I think will be useful for all.



sp_replmonitorhelpsubscription

This helps too much to monitor subscriptions

From BOL


sp_replmonitorhelpsubscription  @publisher = ] 'publisher'
    [ , [ @publisher_db = ] 'publisher_db' ]
    [ , [ @publication = ] 'publication' ]
    [ , [ @publication_type = ] publication_type ]
    [ , [ @mode = ] mode ]
    [ , [ @topnum = ] topnum ]
    [ , [ @exclude_anonymous = ] exclude_anonymous ]
    [ , [ @refreshpolicy = ] refreshpolicy ]


 @publisher Name of @publisher or default is null then  it will display for all publishers that use that distributor

 @publisher_db  Name of publisher Datatbase or is null then all publisher Datatbase

 @publication = Name of publication or default is NULL

 @publication_type

 0                 Transactional publication.
 1                 Snapshot publication.
 2                 Merge publication.
 NULL (default)  Replication tries to determine the publication type.

 @mode  This is most important parameter which serves our objective

0 (default)         Returns all subscriptions.
1                 Returns only subscriptions with errors.
This will give all subscription with errors
2                 Returns only subscriptions that have generated threshold metric warnings.
This will give all subscription which about to cross the  threshold metrics .
3                 Returns only subscriptions that either have errors or have generated threshold metric warnings.
This will give both 1 & 2
4                 Returns the top 25 worst performing subscriptions.
5                 Returns the top 50 worst performing subscriptions.
6                 Returns only subscriptions that are currently being synchronized.
7                 Returns only subscriptions that are not currently being synchronized
If we forgot to start synchronizing then this will help us to find this, with last synchronization time


Lets see one example  I Have modified the server name /db name .

EXEC distribution..Sp_replmonitorhelpsubscription

    @publisher =NULL -- for all publisher

    ,

    @publication_type =0 -- for trnasactional replications

    ,

    @mode =3 -- All which have errors or threshold limit warning

    
   
Now I will stop synchronization for one subscription and we will get it

SELECT Getdate()

EXEC distribution..Sp_replmonitorhelpsubscription

    @publisher =NULL -- for all publisher

    ,

    @publication_type =0 -- for trnasactional replications

    ,

    @mode =7 -- All where synchronization has stopped

 





 

 You can look into following procedures also , which are not so useful for me as of now but can give good information

sp_replmonitorhelppublisher
sp_replmonitorhelppublicationthresholds 
sp_replmonitorhelppublication