more useful BizTalk SQL queries

Posted: April 2, 2012  |  Categories: BizTalk Adminsitration Uncategorized Useful tips
Tags:

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.

SQL

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.

PowerShell

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!

  • Nice one Tord,
    I’ll certainly publish more SQL queries myself as well, as I know from my own experience that they are helpfull in the day-to-day operations.

    Best regards,
    Lex

  • Pingback: BizTalk360 v4.0 BETA available for download - Custom SQL Queries()

  • kranti

    Pls provide me the query to get the tracked message content ..

  • Anil Malik

    Hi,

    In our production, we have lot of interfaces (receive and send port) picking and dropping files onto file share.. And due to performance issue of our file share server, BizTalk interface is getting impacted.

    Can we create query which can indicate performance for receive port for picking and publishing onto messages box?

    • Tord Glad Nordahl

      You can if you want to monitor the Publishing and delivery rate on a host by using the performance monitor and setup a performance collector, when you have collected enough data you can parse them through PAL

    • Tord Glad Nordahl

      I’m not sure if I understand. You can use Performance monitor to monitor throughput if that’s what you meant.

  • Jim Giddings

    Tord,

    Thanks for this blog. I am designing a performance test of a new SOA infrastructure my client is deploying. BizTalk 2013 R2 is the ESB. They’ve deployed a pair of load-balanced, non-clustered servers. It looks to me, from my reading on MSDN, that MessageBox Queries are an important performance factor. How important of a factor are they potentially? Very? Also, will BizTalk Health Monitor track MessageBox Query-related performance issues? Is that what you referred to when you wrote about using a performance monitor and setting up a collector, or were you referring to monitors in general such as PerfMon, SCOM, etc.?

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top