T-SQL: Send Replication Alert along with query to find exact command
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:
- usp_dba_send_cdosysmail: to send email
- 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.