It’s been a while since I last wrote about BizTalk Server on the blog. With so much happening around Azure Integration Services, I naturally focused more on cloud topics and less on BizTalk.
That said, BizTalk Server’s long history—over 25 years—doesn’t make it irrelevant or unworthy of attention. We don’t stop caring about things simply because they’ve matured. I often say I have two technology children: BizTalk Server and Azure Integration Services. I value and enjoy working with both 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.
From time to time, I still return to BizTalk Server topics. Today, I want to share one of my favorite BizTalk Server SQL queries. This query helps you check whether orphaned messages exist in your BizTalk Server environment.
Orphaned messages are messages that remain in the BizTalk environment without a valid subscriber or owning service instance. In simple terms, BizTalk holds the message, but nothing can—or will—process it.
Several factors can cause orphaned messages, and identifying the exact reason is not always easy. Common causes include service instances terminating before message receipt, invalid BTARN manifest data for attachments, duplicate message processing in BTARN, failed MessageBox cleanup jobs, and errors during DTA archive and purge jobs that leave orphaned service instances behind.
What’s important to understand is the impact. If you don’t address these orphaned messages, they can cause performance degradation and unnecessary database growth over time.
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.
