I currently have the below running to monitor replication and send a mail when the threshold of 10000 pendingcmds are breached, however the alert mail is generated every 5 minutes regardless of the threshold. I need assistance to re-code the threshold part as this is not delivering the expected results.
DECLARE @RESULT nvarchar(100)
DECLARE @cmd varchar(100)
SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
EXEC sys.sp_executesql @RESULT
PRINT @RESULT
IF @RESULT > '10000'
Code used to generate alert
USE [TestDB]
create table #pendingcmds
(pendingcmdcount int,
estimatedprocesstime int)
Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
select * FROM OPENROWSET('SQLOLEDB',
'Server=Server\Instance;Trusted_Connection=yes;',
'set fmtonly off;
exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''Server\Instance'',
@publisher_db=''testproduction'',
@publication=''Summary_Inventory_TestProduction'',
@subscriber=''Server'',
@subscriber_db=''TestReplAzure'',
@subscription_type=0')
SELECT * FROM #pendingcmds
--DECLARE @RESULT nvarchar(100)
----DECLARE @cmd varchar(100)
--
--SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds')
--EXEC sys.sp_executesql @RESULT
--PRINT @RESULT
--IF @RESULT > '10000'
--
--BEGIN
/*************************************************************/
/****************** HTML Preparation *************************/
/*************************************************************/
DECLARE @HTML VARCHAR(MAX),
@table VARCHAR(MAX)
SET @HTML = --HTML layout--
'<html><head>' +
'<H1 style="color: #000000">Publisher to Subscriber has exceeded threshold</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FF0000>'+
'<td align=center>Pending CMD Count</b></td>' +
'<td align=center>Estimated Process Time</b></td></tr>';
SELECT @table = CONVERT(nvarchar(max) ,
(SELECT
td = CAST(pendingcmdcount as varchar(30)), '',
td = CAST(estimatedprocesstime as varchar(30)), ''
FROM #pendingcmds
FOR XML PATH(N'tr'), TYPE));
SET @HTML = @HTML + @table + CHAR(10) +
N'</table></body></html>';
SET @HTML = @HTML + '<TR></TR><B>End of Report</B></TABLE></BODY></HTML>';
PRINT @HTML
Declare @MailSubject varchar(100)
SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())
IF LEN(@HTML) > 10
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'tester@test.com',
@body_format = 'HTML',
@body= @HTML,
@subject = @MailSubject,
@profile_name = 'MailRelay'
DROP TABLE #pendingcmds
--END
--ELSE
--DROP TABLE #pendingcmds
--
--END
Best Answer
The following suggestions are not fully tested.
Immediately after
I would delete any rows where your threshold was not met.
Then, I would change your test for whether to send the email or not
From
TO: