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.

If you run this script manually, be sure to run against the correct Database (as per the task description) and set the parameters with correct values.


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