An important part of any monitoring strategy for a BizTalk environment is to monitor the spool depth. The spool is a table in the message box that is used by BizTalk to manage it’s queuing. It gets cleared out by the SQL agent jobs that get installed by BizTalk. Problems with the spool can be an indication that your message box is not able to keep up you might need to think about adding secondary messages boxes. It also can be an indication that some clown didn’t enable the SQL Agent jobs or the agent service isn’t running.
Either way it’s an ugly problem. I’ve seen millions of records in that table and the message box CPU utilization goes thru the roof as the agent tries to clear out the messages whilst BizTalk is trying to access it at the same time. BizTalk will probably start to throttle and I’ve seen environments grind to a halt as a result. DR won’t help as you’ve probably logged shipped all that mess to your backup site! Of course this only gets that ugly in environments that are processing hundreds of thousands or millions of messages but even smaller systems will see negative performance impacts if the spool depth is not controlled.
A nice monitoring option is to use the User Settable Counters in SQL Server. There are perfmon counters for monitoring spool depth but by using the SQL Server you can easily setup an alert that will be sent to the BizTalk operators. Just create stored procedure or just throw the code below into a SQL Agent job that records the spool depth repeatedly throughout the day. You don’t want it to run too frequently, once every 30 minutes might suffice.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_UpdateSpoolCounter] AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
DECLARE @RowCount INT
SELECT @RowCount = COUNT(*) FROM BizTalkMsgBoxSM2DB..spool WITH(NOLOCK)
EXECUTE sp_user_counter1 @RowCount
The last line calls a system stored procedure that saves the value you’re tracking. You can then setup an Alert when the spool depth reaches a particular threshold.
Easy to setup and works nicely.