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 Jump 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:

USE BizTalkMsgBoxDb
SELECT nvcMessageType, count(*) AS MessageCount WITH (NOLOCK)
FROM dbo.Spool
GROUP BY nvcMessageType
ORDER BY MessageCount DESC

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:2870.Result.jpg-550x0.jpg

If you would like more details about this, please read this article on Microsoft TechNet. It was recently awarded BizTalk Technical Guru Gold Award for February 2017.award