A while ago I posted an article about an issue when the tracking information for BizTalk appeared not to be working and it was because the stream status was out of sync and information was not passing from the messagebox database to the tracking database.
https://geekswithblogs.net/michaelstephenson/archive/2008/10/30/126375.aspx
Since then I did a small SQL script which could be used to check if they were in sync and if not could fix the issue. This script is below incase anyone needs it.
Note:
- I havent tested it on BizTalk 2009 just on 2006 and 2006 R2
- It isnt a good idea to use this in a production environment you might want to engage with Microsoft support first
/* This query will validate the stream status and check if its in sync. If it isnt in sync then setting @IncludeUpdate to 1 will get the script to sync back up */
Declare @IncludeUpdate bit Set @IncludeUpdate = 0 -- Set me to 1 to update the values
Declare @TrackingValue0 bigint, @TrackingValue1 bigint, @TrackingValue2 bigint, @TrackingValue3 bigint, @StatusValue0 bigint, @StatusValue1 bigint, @StatusValue2 bigint, @StatusValue3 bigint
Select @TrackingValue0 = Min(SeqNum) From BizTalkMsgBoxDb.dbo.TrackingData_1_0 Select @TrackingValue1 = Min(SeqNum) From BizTalkMsgBoxDb.dbo.TrackingData_1_1 Select @TrackingValue2 = Min(SeqNum) From BizTalkMsgBoxDb.dbo.TrackingData_1_2 Select @TrackingValue3 = Min(SeqNum) From BizTalkMsgBoxDb.dbo.TrackingData_1_3
Select @StatusValue0 = LastSeqNum From BizTalkDtaDb.dbo.TDDS_StreamStatus Where Destinationid = 1 And Partitionid = 0
Select @StatusValue1 = LastSeqNum From BizTalkDtaDb.dbo.TDDS_StreamStatus Where Destinationid = 1 And Partitionid = 1
Select @StatusValue2 = LastSeqNum From BizTalkDtaDb.dbo.TDDS_StreamStatus Where Destinationid = 1 And Partitionid = 2
Select @StatusValue3 = LastSeqNum From BizTalkDtaDb.dbo.TDDS_StreamStatus Where Destinationid = 1 And Partitionid = 3
IF(@TrackingValue0!= @StatusValue0) Begin Print 'The Stream Status for Partition 0 and Destination 1 is ' + Cast(@StatusValue0 as varchar) + ' when it should be ' + Cast(@TrackingValue0 as varchar) End IF(@TrackingValue1!= @StatusValue1) Begin Print 'The Stream Status for Partition 1 and Destination 1 is ' + Cast(@StatusValue1 as varchar) + ' when it should be ' + Cast(@TrackingValue1 as varchar) End IF(@TrackingValue2!= @StatusValue2) Begin Print 'The Stream Status for Partition 2 and Destination 1 is ' + Cast(@StatusValue2 as varchar) + ' when it should be ' + Cast(@TrackingValue2 as varchar) End IF(@TrackingValue3!= @StatusValue3) Begin Print 'The Stream Status for Partition 3 and Destination 1 is ' + Cast(@StatusValue3 as varchar) + ' when it should be ' + Cast(@TrackingValue3 as varchar) End
If(@IncludeUpdate = 1) Begin Print 'The stream status is being synced' Update BizTalkDtaDb.dbo.TDDS_StreamStatus Set LastSeqNum = @TrackingValue0 - 1 Where Destinationid = 1 And Partitionid = 0 Update BizTalkDtaDb.dbo.TDDS_StreamStatus Set LastSeqNum = @TrackingValue1 - 1 Where Destinationid = 1 And Partitionid = 1 Update BizTalkDtaDb.dbo.TDDS_StreamStatus Set LastSeqNum = @TrackingValue2 - 1 Where Destinationid = 1 And Partitionid = 2 Update BizTalkDtaDb.dbo.TDDS_StreamStatus Set LastSeqNum = @TrackingValue3 - 1 Where Destinationid = 1 And Partitionid = 3 Print 'Stream status sync complete' End