MS-SQL Query to get count of Spool size filter by MessageType

Take a scenario you are in middle of Heavy message load flooded in BizTalk system on your Production environment and you want to find which type of message are heavily available in Spool table which need attention to be cleanup or process.

By using the following MS-SQL Query you will get the list of records separated by Message Type in Spool Table with Message Type name.

USE BizTalkMsgBoxDb

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

Hope this Helps!!!!