more useful BizTalk SQL queries

Hello all people! There have been a lot of different questions and a lot of the search strings that ends up here are SQL queries for BizTalk. So I’ve decided to upload a lot of them for you, more are of course coming as time comes.

Most of my contributions are uploaded to the TechNet galleries over there its easier for many to find them. You can download them, ask questions or rate them accordingly.


Find information of orphaned messages
This simple code snippet gives you more information of the orphaned messages, by running this query it can be easier to see what application(s) that are causing the orphaned messages.

Large files in tracking database
I use this script often to see the largest files in the tracking database, how big is the biggest file BizTalk has processed the last couple of days according to the length of storage in the Tracking database

Find recieve locations by address

I often have to search and locate a receive location by a reference, or count receive locations going to a specific EAI Share, this one does the job for me. Gathers name of receive location, receive port and application.

Statistics from in and out events in BizTalk
Gather information of the amount of transactions transferred through BizTalk over a given timeframe.

Instances in Messagebox being processed by a host instance
Gives you information of all instances being processed by a host instance and which one.

Statistics from in out events grouped by portname and date
Gathers information of the amount of transactions being performed in BizTalk over a given timeframe and grouped by portname and date

Find send ports by port address
Find and locate send ports pointing to a specific share, gathers information of send port name and application.

Get statistics from Instances in BizTalk
Get the amount of instances that have been or are under processes in BizTalk over a given timeframe

Count Send Ports by adapters in BizTalk
This one gathers information of all adapter and how many send ports that use this adapter.

Count Receive Location based on adapter usage
This one counts the receive location used by the different adapters in BizTalk.


Stop all applications
Simply stops all applications running in BizTalk

Powershell script to delete old files
This script is mostly used for cleaning up backup and backup folders. 🙂

I’ll get more scripts out there, enjoy!

7 Responses to "more useful BizTalk SQL queries"

  • Lex Hegt says:
  • kranti says:
  • Anil Malik says:
    • Tord Glad Nordahl says:
    • Tord Glad Nordahl says:
  • Jim Giddings says:
Leave a Comment