Introduction

There are predefined alerts that can be used to send alert related with replication failure however that alert requires further investigation to identify root cause. Following lines of codes are customized to send alert with adequate information that can help DBA at any level to address replication issue quickly without spending extra time in investigation.

Script

In order to achieve this we need to create two stored procedures and setup a job to execute usp_dba_ReplAlert:
  1. usp_dba_send_cdosysmail: to send email
  2. usp_dba_ReplAlert: to identify error and send alert using usp_dba_send_cdbosysmail.

Script for usp_dba_sendcdosysmail

/*******************************************************************************************
  
Purpose   : Call usp_dba_send_cdosysmail and provide all necessary information like
        SMTP Server Detail, Subject, Body and compiles right Recipient
        Address etc.
Author    : Microsoft
Modified By : Mohan Kumar
Created on  : 07/24/2006
  
dependencies:
        Table            Procedure
  
Usage    :
        usp_dba_send_cdosysmail
            @From='mohan@sqlserverexperts.com',
            @To = 'mohan@sqlserverexperts.com',
            @Subject = 'Test Email',
            @Body = 'Testing....',
            @SMTPServer = 'MyTestSMTP'
  
Note     : Server Name gets automatically added in subject line.
*******************************************************************************************/
CREATE PROCEDURE [dbo].[usp_dba_send_cdosysmail]
(
   @From   varchar(200),
   @To    varchar(2000),
   @CC    varchar(1000) = NULL,
   @BCC   varchar(1000) = NULL,
   @Subject varchar(200) = '',
   @Body   nvarchar(4000)= '',
   @SMTPServer varchar(50),
   @Body1   varchar(8000) = ' ',
   @Attachments varchar(8000) =''
)
AS
DECLARE @iMsg int
DECLARE @hr int
DECLARE @Source varchar(255)
DECLARE @Description varchar(500)
DECLARE @Output varchar(1000)
Declare @filename varchar(150)
  
  
set @Subject = @@ServerName + ':  ' +  @Subject
  
-- Create the CDO.Message Object
  
EXEC @hr = master.dbo.sp_OACreate 'CDO.Message', @iMsg OUT
  
/*
  Configuring the Message Object
    
  This is to configure a remote SMTP server.
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
  1 = LOCAL, 2 = REMOTE
*/
  
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
  
/*
  This is to configure the Server Name or IP address.
  Replace MailServerName by the name or IP of your SMTP Server.
  I set the IP for my LOCAL SMTP BOX
*/
  
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
  
-- Save the configurations to the message object.
  
EXEC @hr = master.dbo.sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
  
-- Set the e-mail parameters.
  
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'CC', @CC
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'BCC',@BCC
EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'Subject', @Subject
  
--
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
--declare @Body2 text
--set @Body2 = @Body + @Body1
declare @vMsg varchar(100)
set @vMsg = convert(varchar,@iMsg)
  
IF @Body LIKE '%<HTML>%'
begin
  --EXEC @hr =
--print 'master.dbo.sp_OASetProperty ' +  convert(varchar,@iMsg) + ',''HTMLBody'','''+ @Body + @Body1 + ''''
  exec('master.dbo.sp_OASetProperty ' +  @vMsg + ',''HTMLBody'','''+ @Body + @Body1 + '''')
    
  set @hr = 0
end
ELSE
  EXEC @hr = master.dbo.sp_OASetProperty @iMsg, 'TextBody', @Body
  
print @Attachments
IF @Attachments != ''
begin
  while len(@Attachments) > 0
  begin
  
    set @filename = left(@Attachments, charindex(',',@Attachments,1)-1)
    set @Attachments = replace(@Attachments, @filename + ',','')
      
    EXEC @hr = master.dbo.sp_OAMethod @iMsg, 'AddAttachment',NULL, @filename
  
  end
end
  
EXEC @hr = master.dbo.sp_OAMethod @iMsg, 'Send', NULL
  
-- Sample error handling.
IF @hr <> 0
BEGIN
   SELECT @hr
   EXEC @hr = master.dbo.sp_OAGetErrorInfo NULL, @Source OUT, @Description OUT
   IF @hr = 0
   BEGIN
      SELECT @Output = '  Source: ' + @Source
      PRINT  @Output
      SELECT @Output = '  Description: ' + @Description
      PRINT  @Output
   END
   ELSE
   BEGIN
      PRINT '  master.dbo.sp_OAGetErrorInfo failed.'
      RETURN
   END
END
  
/*
  Do some error handling after each step if you need to.
  Clean up the objects created.
*/
EXEC @hr = master.dbo.sp_OADestroy @iMsg
  
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

Script for usp_dba_ReplAlert

/*-------------------------------------------------------------------------------------------------------
Purpose   :  Send Replication Failure Alert along with query to determine exact command causing failure
Author    :  Mohan Kumar
Created on  :  09/18/2006
Dependencies:
    Tables / Views
    ===================================
    msdb..sysreplicationalerts
    Distribution.dbo.MSrepl_errors
    distribution.dbo.MSsubscriptions
      
    Procedures / Functions
    ===================================
    dbo.usp_dba_send_cdosysmail
      
usage:
exec dbo.usp_dba_ReplAlert
  
*/---------------------------------------------------------------------------------------------------------------------------------
  
CREATE procedure [dbo].[usp_dba_ReplAlert]
as
  set nocount on
  declare  @publisher   sysname
      ,@publisher_db  sysname
      ,@publication  sysname
      ,@article    sysname
      ,@subscriber   sysname
      ,@subscriber_db sysname
      ,@alert_id    int
      ,@ErrorDesc   varchar(max)
      ,@Body      varchar(max)
      ,@minTime    varchar(10)
      ,@maxTime    varchar(10)
      ,@error_id    int
      ,@publisher_database_id tinyint
      ,@xact_seqno nvarchar(max)
      ,@cmdid smallint
      ,@FindCmdString nvarchar(max)
  
  declare cur cursor local for
        select  distinct publisher
            ,s.publisher_db
            ,d.publication
            ,subscriber
            ,s.subscriber_db
            ,right(convert(varchar(max),alert_error_text),len(convert(varchar(max),alert_error_text)) - (charindex('failed',convert(varchar(max),alert_error_text),1)+6))
            ,publisher_database_id
            ,error_id
      from   msdb..sysreplicationalerts s (nolock) join distribution..MSdistribution_agents d (nolock)
        on  s.agent_id  = d.id
      where  alert_error_code = 14151 and status = 0
  
    for read only
    open cur
      
  fetch cur into  @publisher
          ,@publisher_db
          ,@publication
          ,@subscriber
          ,@subscriber_db
          ,@ErrorDesc
          ,@publisher_database_id
          ,@error_id
      
  while (@@fetch_status = 0)
  begin
  
    set @Body = '<html><body>'
    set @Body = @Body + '<table border="0">'
    set @Body = @Body + '<tr style="background:#FF99CC"><td colspan=3>Replication Alert as on ' + convert(varchar,getdate(),107) + ' at ' + convert(varchar,getdate(),108) + '</td></tr>'
    set @Body = @Body + '<tr>'
    set @Body = @Body + '<td>Publisher Server</td>'
    set @Body = @Body + '<td>:</td><td>' + @publisher + '</td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td>Publisher DB</td>'
    set @Body = @Body + '<td>:</td><td>' + @publisher_db + '</td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td>Subscriber Server</td>'
    set @Body = @Body + '<td>:</td><td>' + @subscriber + '</td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td>Subscriber DB</td>'
    set @Body = @Body + '<td>:</td><td>' + @subscriber_db + '</td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td>Publication</td>'
    set @Body = @Body + '<td>:</td><td>' + @publication + '</td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td><font color = red> Error Description</font></td>'
    set @Body = @Body + '<td> </td>'
    set @Body = @Body + '</tr><tr>'
    set @Body = @Body + '<td colspan=3><font color=red>' + replace(@ErrorDesc,'''','"') + '</font></td>'
  
  
/********** get sp_browsereplcmds command string for help *************/
    select
       @xact_seqno = CONVERT(nvarchar(max),xact_seqno,1)
      ,@cmdid = command_id
      from Distribution.dbo.MSrepl_errors where id=@error_id
  
    set @FindCmdString = '<font color=blue>exec </font><font color=darkred>sp_browsereplcmds </font> @xact_seqno_start = <font color=red>''''' + CONVERT(nvarchar(max),@xact_seqno,1) + '''''</font>'
    set @FindCmdString= @FindCmdString + ',@xact_seqno_end = <font color=red>''''' + CONVERT(nvarchar(max),@xact_seqno,1) + '''''</font>'
    set @FindCmdString= @FindCmdString + ',@publisher_database_id = ' + CONVERT(varchar,@publisher_database_id)
    set @FindCmdString= @FindCmdString + ',@command_id = ' + CONVERT(varchar,@cmdid)
    --print @FindCmdString
      
  --  print @Body
      
    if @Body like '%The subscription(s) have been marked inactive%'
    begin
      update  distribution.dbo.MSsubscriptions
      set   status = 2
      where  status = 0
  
        
    end
    else
    begin
      
      set @Body = @Body + '</tr><tr>'
      set @Body = @Body + '<td colspan=3><font color=blue> <p><b>DBA, Please use following query to find command throwing error: </b> <br></font> ' + @FindCmdString + '</td>'
        
  
    end
      
    set @Body = @Body + '</tr></table>'  
    set @Body = @Body + '</body></html>'  
  
      
    -- Change From, To, and SMTP Server name accordingly to your requirement and environment.
      
    exec dbo.usp_dba_send_cdosysmail
      @From='mohan@sqlserverexperts.com',
      @To = 'mohan@sqlserverexperts.com',
      @Subject = 'Replication Error',
      @Body = @Body,
      @SMTPServer = 'MyTestSMTP'
       
         fetch cur into  @publisher
            ,@publisher_db
            ,@publication
            ,@subscriber
            ,@subscriber_db
            ,@ErrorDesc
            ,@publisher_database_id
            ,@error_id
  end
  close cur
  deallocate cur
  
  update msdb.dbo.sysreplicationalerts set status = 1
  where status = 0

Note

1.It is highly advised to test it in your environment before implementing. 
2.Check test mail working before implementing alert.