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