It’s been a while since I’ve talked about BizTalk Server on my blog. And the main reason is that with so much happening in Azure Integration Services, I often left the BizTalk Server topics behind. However, just because BizTalk Server is already an established platform (with a 25-year history) doesn’t mean we should stop using it or stop liking it. We also don’t stop loving our children because they are already adults or have younger siblings. I have two technologies children: BizTalk Server and Azure Integration Services, and I like them equally.
📝 One-Minute Brief
Get access to a simple BizTalk Server SQL Query that allows you to check if there are Orphaned Messages in your BizTalk Server environment.
So, from time to time or when I will always return to BizTalk Server, and today I’m going to share one of my favorite BizTalk Server SQL queries: a SQL Query to check if there are Orphaned Messages in our BizTalk Server environment.
Orphaned messages are items left in the BizTalk environment that no longer have a valid subscriber or owning service instance. In short, BizTalk is holding a message, but nothing can (or will) pick it up.
The reasons for orphaned messages to happen are sometimes unclear or more challenging to understand. However, they can be caused by service instance termination before message receipt, invalid BTARN manifest data for attachments, duplicate message processing within BTARN, failed MessageBox cleanup jobs, errors during DTA archive and purge jobs that leave orphaned DTA service instances, and many more.
What is essential to understand is that these issues can lead to performance problems and excessive database growth if not addressed.
BizTalk Server: SQL Query to check if exists Orphaned Messages
This is a simple SQL Server Query that checks if there are Orphaned Messages in your BizTalk Server environment. You can execute it safely without the need to stop your BizTalk Server environment, and you can also schedule it to run periodically.
USE [biztalkDTADb]
SELECT * FROM [dbo].[dta_ServiceInstances]
WHERE dtEndTime is NULL AND
[uidServiceInstanceId] NOT IN(SELECT [uidInstanceID]
FROM BizTalkMsgBoxDb.[dbo].[Instances] WITH (NOLOCK)
UNION SELECT [StreamID]
FROM BizTalkMsgBoxDb.[dbo].[TrackingData] WITH (NOLOCK))
THIS SQL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.
I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can consider buying (or helping me buy) my son a Star Wars Lego set.
