Keep an eye on replication error in SQL Server
Setting up replication is may be little easy but to keep a constant watch on the performance of the replication is one of the tough task to do. If setup of replication is proper and if you are having a proper hardware and infrastructure, you won’t face much issue. Since we are not facing much issue, we may feel hectic to keep constant watch on replication health and this is the time you may face some trouble in replication and you are not alert to handle that uncertain situation.
DBA can’t afford to be in this situation. Every DBA has to prepare their self ready to handle as much uncertain situation as possible. I have created one small query to run in “Distributor” database of replication which show me error message with publisher, subscriber name and exact date time if anything goes wrong with my replication.
I used to call following SELECT query in my monitoring application so that if any error comes, I get immediate update from the person who is manually monitoring my application in the company. “Prevention is always better then cure” but nobody is sure enough that after having prevention, there won’t be any situation arise which force us to have cure. This is the reason I keep adding things in my monitoring application though I used to take as much prevention as possible while setting up things.
SELECT ma.publisher_db, ma.publication, ma.subscriber_db, msre.time, msre.error_text FROM MSrepl_errors msre INNER JOIN MSdistribution_history msh ON (msre.id = msh.error_id) INNER JOIN MSdistribution_agents ma ON (ma.id = msh.agent_id) ORDER BY msre.time DESC
Above SELECT query will return the result only, if we have any error in replication at the moment, otherwise you will get blank result set.
Over the time, I have written quite a few articles on the subject of “Replication”, If you wish to refer any of them, have a look at list given below:
- Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
- Remove Replication from SQL Server 2012 (Click Here)
- Error Fix – Cannot execute as the database principal(Click Here)
- Setup Transaction Replication in SQL Server 2012 (Click Here)
- Add new article in transactional replication in SQL Server 2012 (Click Here)
- Delete article from transactional replication in SQL Server 2012 (Click Here)
- Find highest latency in transactional replication in SQL Server 2012 (Click Here)
- Be familiar with important terms of replication in SQL Server (Click Here)
- Find Object participated in replication (Click Here)
- Add table in transactional replication from script in SQL Server (Click Here)
- Add stored procedure in transactional replication by script in SQL Server (click Here)
- Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
- Move replicated database file to new location in SQL Server 2012 (Click Here)
- Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
- Insert Tracer Token to see replication latency (Click Here)
- Generate script to add multiple table in replication as article (Click Here)
- Find undistributed replication command in SQL Server (Click Here)
- Keep watch on replication undistributed command in SQL Server via SSIS (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.