What is BizTalk Spool table?
Spool table is “The Important” table in the BizTalk Message Box database, all the messages that goes through the system will touch the spool table. Growth of the spool table will fluctuate heavily depending on the type of processing your are doing. Example: If you are debatching a large message, all of a sudden you’ll seen 100 or 1000’s of entries in the spool table and once the processing is complete, you’ll see everything cleared. The important aspect here is, your processing servers (orchestrations, send ports) is able to keep up with the load and clear the messages as soon as possible. In a healthy environment, the size of the spool table will be pretty much zero, unless you have some long running orchestrations and errors. If the size of the spool table is growing linearly (not reducing, but just going up and up), then there is a problem in your environment. It could be SQL locking, or your application servers are not clearing messages fast enough, or your external line of business applications are not processing fast enough, too many errors etc. In those cases it’s important to understand the bottleneck and try to rectify it. Spool table is global across all host, and every host will have is own queue table (ex: BizTalkServerApplicationQ), it may be worth checking depth of each one of them to find the culprit.
How to monitor Spool table?
There are couple of ways you can monitor the growth of spool table in BizTalk Server. You can monitor the performance counter “Message Box:General Counters /Spool size
” from anyone of your BizTalk Servers, or simply you can execute one the following SQL in the BizTalk message box database.
SELECT count(*) from SPOOL WITH (NOLOCK)
SELECT top 1 rows FROM sys.partitions WHERE object_id = object_id(‘spool’)
The NOLOCK keyword is important in first query, you don’t want to put any locks in the spool table while measuring the row count. The second query is the one used by the performance counter “Spool Size” using the stored procedure “MsgBoxPerfCounters_GetSpoolSize
Monitoring Spool depth using BizTalk360
BizTalk360 comes with a generic Database query monitoring especially designed to address scenarios like this. Below picture shows the monitoring screen for the Database query.
You need to supply the following details
- Alert Name: Ex: Spool Depth (any friendly name)
- SQL Instance Name
- Database Name
- SQL Query: Ex: SELECT COUNT(*) FROM SPOOL WITH (NOLOCK). The query should return a scalar value (string, int or boolean) and based on the return value you can set the expected warning and error conditions.
In this example we set the warning level to be “Greater than equal to 40” and error level to be “Greater than equal to 60”. You are free to setup any value based on your environment condition.
Once configured and “Save” button is pressed the UI will give immediate feedback checking the condition and showing the status. As shown in the below picture, you can notice the current count in spool table is 65, which violated our error condition “Greater than equal to 60” and overall status went to Critical.
The values will also reflect on the monitoring dashboard as shown in the below picture. The following dashboard is just an example we set for spool depth, but you can fully customize it by adding more sections to the same dashboard (ex: Receive Locations, Send ports, orchestrations, disks, NT services, etc.). The dashboard will refresh automatically and highlight the latest information.
You will also receive an email notification as shown below. Again similar to Monitoring dashboard again you can customise the information you expect in the email by adding more sections like Receive Locations, Send ports, orchestrations, disks, NT services etc.
Hope this articles gives you an idea of how you can monitor the spool depth. The plug-in very generic and you can use the same technique to monitor variety of technical and business related data from your SQL database server. Example: Monitoring the message in/out events in tracking database, monitoring activites/view in BAM databases, monitoring the depth of each host queue tables or even monitoring certain things in your own custom databases.