Monitoring Biztalk

We do not have any fancy system monitoring tool, we still need to track if something is going wrong in BizTalk, to my experience it is most likely either suspended messages or a receive port that shuts down

The below script will check for that and send a mail alerting me about the status, not very fancy but it does the trick

-- Query if any messages are suspended

declare @Status int

declare @messages varchar(256)
declare @msgbody varchar(max)

select @messages = COUNT(*) from [dbo].[InstancesSuspended]

select @msgbody = 'There are ' + @messages + ' suspended messages' + CHAR(13)

if @messages != 0

begin

      exec sp_send_dbmail @profile_name ='ProfileName'

      ,@recipients = 'name@company.com'

      ,@subject ='Error in BizTalk application, Suspended messages'

      ,@body = @msgbody

end

-- Query for any receive ports that are disabled

declare @Status2 int

declare @ports varchar(256)

declare @msgbody2 varchar(max)

select @msgbody2 = 'The following receive ports are disabled:' + CHAR(13)

select @Status2 = COUNT(*) from [dbo].[adm_ReceiveLocation] where [Disabled] != 0

 

if @Status2 != 0

begin

declare port_cursor cursor FOR

select name from [dbo].[adm_ReceiveLocation] where [Disabled] != 0;

open port_cursor

      fetch next from port_cursor into @ports

            while @@FETCH_STATUS = 0

            begin

                  select @msgbody2 = @msgbody2 + @ports + CHAR(13)

                  fetch next from port_cursor into @ports;

            end

            close port_cursor;

            deallocate port_cursor;   

begin

      exec sp_send_dbmail @profile_name ='ProfileName'

      ,@recipients = 'name@company.com'

      ,@subject ='Error in BizTalk application, receive port(s) are disabled'

      ,@body = @msgbody2

end

end

 

I have put this script in a SQL job that runs every hour, that should do it.