Home > SQL Server Replication > Log Reader Agent Status Notification

Log Reader Agent Status Notification

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: