none
MSrepl_errors details for particular publishers/subscriber ?

    Question

  • HI Team,

    I do have 23 publishers and some of the publishers have 1 to 15 subscribers.

    All are transnational( one way)/push subscriptions . 

    when I browse the below error table in distribution database i do get error codes. But is there any way to find the publisher/subscriber which is causing those errors?

    I was using the below command to get the error details.

    SELECT top 100 *  FROM distribution.dbo.MSrepl_errors ORDER BY TIME

    Please help me ...



    Sunday, September 22, 2013 11:31 AM

Answers

  • Hi SQLDBA_repl,

    Have a look at MSdistribution_history, MSdistribution_agents, MSrepl_errors, and master.sys.servers.  MSdistribution_history has error_id, which is the ID of the error in MSrepl_errors, and agent_id which is the ID of the distribution agent.  MSdistribution_agents has publisher_id and subscriber_id which we can use to query master.sys.servers for publisher and subscriber name.  For example:

    USE distribution
    GO
    
    SELECT 
    	publisher.name, 
    	MSda.publication, 
    	MSda.publisher_db, 
    	subscriber.name, 
    	MSda.subscriber_db,
    	MSre.error_code,
    	MSre.error_text
    FROM MSdistribution_history MSdh
    INNER JOIN MSdistribution_agents MSda ON MSdh.agent_id = MSda.id
    INNER JOIN MSrepl_errors MSre ON MSdh.error_id = MSre.id
    INNER JOIN master.sys.servers publisher ON MSda.publisher_id = publisher.server_id
    INNER JOIN master.sys.servers subscriber ON MSda.subscriber_id = subscriber.server_id
    WHERE MSdh.error_id <> 0

    Hope this helps.


    Brandon Williams (blog | linkedin)

    • Marked as answer by SQLDBA_repl Monday, September 23, 2013 10:12 PM
    Monday, September 23, 2013 1:54 AM

All replies

  • Hi,

    Below link will give you information about replication monitor which will help you in identifying the errors:

    http://technet.microsoft.com/en-us/library/ms152505%28v=sql.105%29.aspx

    For different replication error codes:

    http://technet.microsoft.com/en-us/library/ms152467.aspx

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, September 22, 2013 2:34 PM
  • Hi SQLDBA_repl,

    Have a look at MSdistribution_history, MSdistribution_agents, MSrepl_errors, and master.sys.servers.  MSdistribution_history has error_id, which is the ID of the error in MSrepl_errors, and agent_id which is the ID of the distribution agent.  MSdistribution_agents has publisher_id and subscriber_id which we can use to query master.sys.servers for publisher and subscriber name.  For example:

    USE distribution
    GO
    
    SELECT 
    	publisher.name, 
    	MSda.publication, 
    	MSda.publisher_db, 
    	subscriber.name, 
    	MSda.subscriber_db,
    	MSre.error_code,
    	MSre.error_text
    FROM MSdistribution_history MSdh
    INNER JOIN MSdistribution_agents MSda ON MSdh.agent_id = MSda.id
    INNER JOIN MSrepl_errors MSre ON MSdh.error_id = MSre.id
    INNER JOIN master.sys.servers publisher ON MSda.publisher_id = publisher.server_id
    INNER JOIN master.sys.servers subscriber ON MSda.subscriber_id = subscriber.server_id
    WHERE MSdh.error_id <> 0

    Hope this helps.


    Brandon Williams (blog | linkedin)

    • Marked as answer by SQLDBA_repl Monday, September 23, 2013 10:12 PM
    Monday, September 23, 2013 1:54 AM
  • Thanks all for your hlep
    Monday, September 23, 2013 10:13 PM