While setting up critical Transactional Replication I came across this scenario of alerting the L3 DBA support team with Log Reader Agent Status and writing it to SQL Server Error Log.
When Log Reader Agent is Retrying to read the Database Log and sending it to Distribution Database and when it fails.
To achieve this I have written an after insert trigger on table
MSlogreader_history in Distribution Database.
To get the last status of the Log Reader Agent execute the below code on distributor database.
SELECT comments,runstatus FROM distribution.dbo.MSlogreader_history where timestamp=(SELECT MAX(timestamp) from MSlogreader_history)
—Trigger Code
CREATE TRIGGER LogReaderStatus ON MSlogreader_history AFTER INSERT AS BEGIN DECLARE @LogReaderStatus int DECLARE @LogReaderComments varchar(255) DECLARE @LogReader varchar(255) DECLARE @Status varchar(255)SELECT @LogReaderComments=lrh.comments,@LogReaderStatus=lrh.runstatus, @LogReaderComments=lra.name FROM distribution.dbo.MSlogreader_history lrh INNER JOIN MSlogreader_agents lra ON lrh.agent_id=lra.id and lrh.timestamp=(SELECT MAX(i.timestamp) from INSERTED i) IF @LogReaderStatus=5 SET @@Status='Log Reader Agent : '+ @LogReader+' is retrying.....' BEGIN EXEC xp_logevent 60000, @@Status, informational END IF @LogReaderStatus=6 SET @@Status='Log Reader Agent : '+ @LogReader+' Failed.......' BEGIN EXEC xp_logevent 60000,@@Status, informational END END GO