The Spool table is a table in BizTalkMsgBoxDb. Having a large record count indicates a lot of active, dehydrated or suspended messages. It is important to avoid large and growing Spool size, as this can cause all kinds of problems in your BizTalk environment. This simple script checks the Message Type and count of each, so you can see what kinds of messages are in the Spool table. Appropriate actions can then be taken to resolve the issues.
The T-SQL looks like this:
nvcMessageType is the actual Message Type in the Spool table, and MessageCount will display the number of each Message Type. The results are ordered descending by MessageCount.
The result may look like this: