Keep an eye on replication error in SQL Server

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.

[sourcecode language=”sql”]
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
[/sourcecode]

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)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Keep an eye on replication error in SQL Server”

Comments are closed.