Remove BizTalk orphans from tracking

USE [biztalkDTADb]


UPDATE
    [dbo].[dta_ServiceInstances]

SET
    [dtEndTime] = GetUTCDate()
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)
    )