April 22, 2013 at 4:22 am
Hello,
Is there a way to match replication errors in the MSREPL_ERRORS table to its specific subscribers?
Scenario:
- 1 publishing server
- 100 subscribing servers
If I connect to the publishing server and SELECT * FROM MSREPL_ERRORS, I can see a list of errors.
However, I do not know which of the 100 servers encountered this error.
For example, seeing a Disk Space Error does not really help because I do not know which server had this error.
Any ideas/suggestions?
Thanks in advance!
Rafferty
April 23, 2013 at 4:01 am
Hello,
Merge replication:
USE distribution
go
SELECT MSmerge_agents.subscriber_name ,
*
FROM MSrepl_errors
JOIN MSmerge_history ON MSrepl_errors.id = MSmerge_history.error_id
JOIN MSmerge_agents ON MSmerge_agents.id = MSmerge_history.agent_id
ORDER BY MSrepl_errors.time DESC
Don't have transactional replication to test, should be
USE distribution
go
SELECT MSdistribution_agents.subscriber_name ,
*
FROM MSrepl_errors
JOIN MSdistribution_history ON MSrepl_errors.id = MSdistribution_history.error_id
JOIN MSdistribution_agents ON MSdistribution_agents.id = MSdistribution_history.agent_id
ORDER BY MSrepl_errors.time DESC
If the subscriber_name returns null, use the MSdistribution_agents.Name or MSmerge_agents.Name column for analisys
Best Regards,
Vladimir
April 23, 2013 at 4:58 am
Hi,
Thank you for your reply.
Our msdistribution_agents and msdistribution_history tables are empty.
So what I did was to join msrepl_errors with msmerge_history according to the session_id, and then join again with msmerge_agents
Can I assume that joining on the session_id of these msmerge_history and msrepl_errors is okay?
I'm having doubts because not all session_ids exist in msmerge_history, thus I end up doing a LEFT JOIN.
April 23, 2013 at 6:51 am
Hi,
msdistribution_agents and msdistribution_history tables are empty, because I think these tables are used for the transactional replication only.
You are right, left join to MSmerge_agents is needed, but I am not sure about the session_id. I think the right key is the error_id
Let's compare MSmerge_history with error>0 and MSrepl_errors tables.
SELECT * FROM MSmerge_history WHERE error_id>0 ORDER BY time desc
SELECT * FROM MSrepl_errors ORDER BY time desc
MSmerge_history table has unique error_id, table MSrepl_errors not. It looks like the history information 1st comes to the MSmerge_history table, see the time columns in both tables and then more descriptive data to the MSrepl_errors table. Maybe it's even better to concatenate the string with error codes and texts from MSrepl_errors by error_id and use it for automation analysis.
I am personally not using the history table directly, because of the history retention policy. I have created a table in the local DBA_DB database and scheduled the job to run every 5 minutes. My monitoring system reads this table and sends customized alerts with the status in (5,6) or IsError >0. I can also keep the replication history much longer.
There is a query which I am using for automation:
SET nocount ON;
exec distribution.dbo.sp_replmonitorrefreshjob @iterations=1;
SELECT MSreplication_monitordata.Publisher ,
MSreplication_monitordata.publisher_db ,
MSreplication_monitordata.publication ,
MSreplication_monitordata.agent_type ,
CASE WHEN MSreplication_monitordata.agent_type = 4 THEN 'Merge Agent'
ELSE ''
END [agent_type] ,
ISNULL(( SELECT Name
FROM sys.servers
WHERE server_id = MSmerge_agents.Subscriber_ID
), '') [Subscriber] ,
MSmerge_agents.Subscriber_DB ,
status ,
CASE WHEN status IN ( 5, 6 )
OR comments = 'Retrieving subscription information.' THEN 1
ELSE 0
END [IsError] ,
comments [LastInformationMessage] ,
MSreplication_monitordata.last_distsync [LastSync] ,
ISNULL(MSreplication_monitordata.cur_latency,0) [latency]
FROM distribution.dbo.MSreplication_monitordata
LEFT JOIN distribution.dbo.MSmerge_agents ON MSmerge_agents.id = MSreplication_monitordata.agent_id
LEFT JOIN distribution.dbo.MSmerge_history ON MSreplication_monitordata.agent_id = MSmerge_history.agent_id
JOIN ( SELECT agent_id ,
MAX(ISNULL(timestamp, 0)) timestamp
FROM distribution.dbo.MSmerge_history
GROUP BY agent_id
) a ON MSmerge_history.agent_id = a.agent_id
AND a.timestamp = MSmerge_history.timestamp
WHERE MSreplication_monitordata.agent_type IN ( 4 )
Best Regards,
Vladimir
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy