Here is the stored procedure that needs to placed on the same server (but different database) as the MessageBoxDb. Then you simply have a job that runs this stored procedure (again, it never should need to be turned off)
This will check for new suspended messages every 30 seconds.
CREATE PROCEDURE [dbo].[MonitorBTSMessages]
AS
BEGIN
SET NOCOUNT ON;
while(1=1)
BEGIN
WAITFOR DELAY '00:00:30'
DECLARE @SUSPENDEDMESSAGES INT
SELECT @SUSPENDEDMESSAGES=COUNT(*)
FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
IF (@SUSPENDEDMESSAGES>0)
BEGIN
declare @thisSubject varchar(100)
select top 1 @thisSubject='Suspended message on '+ nvcErrorProcessingServer
FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
and nErrorCategory=0
Declare @body1 varchar(8000)
SELECT @body1 = ISNULL(@body1+'Adapter: '+nvcAdapter+' at '+nvcURI+'<br />Description: '+nvcErrorDescription+'<br /><br />','')
FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended]
WHERE DtCreated between DATEADD(s,-30,GETUTCDATE()) and GETUTCDATE()
and nErrorCategory=0
ORDER BY nvcErrorDescription
EXEC msdb.dbo.sp_send_dbmail @recipients='support@company.com',
@copy_recipients ='supervisor@company.com;manager@company.com',
@subject = @thisSubject,
@body = @body1,
@body_format = 'HTML' ;
END
END
END