Find all BizTalk send ports not in use

If you didn’t know you can query the configuration of your BizTalk install from the BizTalk management database.

Here’s a query to find all send ports not connected to anything, and therefore are no longer used.

USE BizTalkMgmtDb

GO

— find all send ports not in use

SELECT

    SP.nvcName AS SendPortName , APP.nvcName AS ApplicationName

   FROM bts_sendport AS SP

     INNER JOIN bts_application AS APP ON SP.nApplicationID = APP.nID

   WHERE NOT EXISTS( SELECT 1 FROM [bts_spg_sendport] WHERE nSendPortID = SP.[nID] )     — not in a send port group

       AND CONVERT(VARCHAR(MAX), nvcFilter) = ”                                        — has no filter

       AND  NOT EXISTS( SELECT 1 FROM [bts_orchestration_port_binding] WHERE nSendPortID = SP.[nID] )  — not connected to an orchestration