Share via

BizTalk: Check Contents of MessageBox Spool Table

  • Article

Introduction

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.

Details

The T-SQL looks like this:


  

      USE BizTalkMsgBoxDb  
      SELECT nvcMessageType, count(*) AS  MessageCount
      FROM dbo.Spool WITH (NOLOCK)
      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:

[

](resources/2870.Result.jpg)
If you need a total count in the Spool table, run the following script:



      USE BizTalkMsgBoxDb  
      SELECT count(*) from dbo.Spool WITH (NOLOCK)

Instructions

Connect to the SQL instance where the BizTalkMsgBoxDb database is installed. Open a new query, paste the T-SQL above, and run it.

See Also

Read suggested related topics:

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.