Due to the limitations in BizTalk360, you cannot monitor the orphaned messages from the UI. But you can get the number of orphaned messages in the database by executing the below SQL query in the SQL server management studio.
SQL Query
declare @mycount int
declare @msg nvarchar(2048)
declare @servername nvarchar(256)
declare @name nvarchar(256)
declare @dbname nvarchar(256)
declare @crlf nvarchar(10)
declare @srvversion nvarchar(32)
declare @errortext nvarchar(1024)
--***********************************************************
set @servername = ' '
set @dbname = ' '
--***********************************************************
set @crlf = char(13) + char(10)
set @msg = ''
set @srvversion = convert(nvarchar,SERVERPROPERTY('productversion'))
if (charindex('8',@srvversion) = 1) -- code for SQL2000 since it can't handle sys.servers
begin
select @mycount = count(*) from master..sysservers where srvname = @servername
if @mycount != 1
BEGIN
declare SysServerCursor cursor global FOR SELECT srvname from master..sysservers with (nolock)
open SysServerCursor
FETCH NEXT FROM SysServerCursor INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @msg = @msg + @crlf + ' - ' + @name
FETCH NEXT FROM SysServerCursor INTO @name
END
close SysServerCursor
deallocate SysServerCursor
set @errortext = 'Could not find '+@servername+' in sysservers. Could only find these:'+@msg+@crlf+@crlf+'If the SQL server is using a case-sensitive collation, you may need to hit the Disconnect button so you can connect again after typing in the SQL connection info in the correct case.'+@crlf+@crlf+'If necessary, execute the stored procedure sp_addlinkedserver to add the server to sysservers.'
RAISERROR (@errortext, 11, 2)
END
ELSE
BEGIN
exec('
select ''Found ''+cast ((Select count (*) from [dbo].[dta_ServiceInstances] with (nolock)
WHERE [dtEndTime] is NULL
AND [uidServiceInstanceId] NOT IN (
SELECT [uidInstanceID] FROM ['+@servername+'].['+@dbname+'].[dbo].[Instances] --with (nolock) (SQL2000 does not support locking hints over linked server call)
UNION
SELECT [StreamID] FROM ['+@servername+'].['+@dbname+'].[dbo].[TrackingData] --with (nolock) (SQL2000 does not support locking hints over linked server call)
)) as nvarchar(10))+'' Orphaned Service Instances in DTA.'' as Result
')
END
end
else -- code for SQL2005 & above since it uses sys.server
begin
select @mycount = count(*) from sys.servers where name = @servername
if @mycount != 1
BEGIN
declare SysServerCursor cursor global FOR SELECT name from sys.servers with (nolock)
open SysServerCursor
FETCH NEXT FROM SysServerCursor INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @msg = @msg + @crlf + ' - ' + @name
FETCH NEXT FROM SysServerCursor INTO @name
END
close SysServerCursor
deallocate SysServerCursor
set @errortext = 'Could not find '+@servername+' in sys.servers. Could only find these:'+@msg+@crlf+@crlf+'If the SQL server is using a case-sensitive collation, you may need to hit the Disconnect button so you can connect again after typing in the SQL connection info in the correct case.'+@crlf+@crlf+'If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.'
RAISERROR (@errortext, 11, 2)
END
ELSE
BEGIN
exec('
select ''Found ''+cast ((Select count (*) from [dbo].[dta_ServiceInstances] with (nolock)
WHERE [dtEndTime] is NULL
AND [uidServiceInstanceId] NOT IN (
SELECT [uidInstanceID] FROM ['+@servername+'].['+@dbname+'].[dbo].[Instances] with (nolock)
UNION
SELECT [StreamID] FROM ['+@servername+'].['+@dbname+'].[dbo].[TrackingData] with (nolock)
)) as nvarchar(10))+'' Orphaned Service Instances in DTA.'' as Result
')
END
end