Geeks With Blogs

News View Michael Stephenson's profile on BizTalk Blog Doc View Michael Stephenson's profile on LinkedIn
Michael Stephenson keeping your feet on premise while your heads in the cloud

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.

http://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
 

 

Posted on Tuesday, January 12, 2010 6:14 PM BizTalk , blogdoc | Back to top


Comments on this post: Missing Tracking Data problem followup from previous post

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
1. Value in TDDS_StreamStatus can be higher than min(seqnum) in Message box. If you change last seqnum, TDDS will move tracking data again from message box to DTA / BAM.
2. This script will work only if your BizTalk system have one message box.
3. This script should be run after stopping all host instances (which has "Allow host tracking" option is enabled)

TDDS failing to move tracking data due to large gaps in these numbers is fixed in BizTalk 2006 R2. (http://support.microsoft.com/kb/969870/en-us).

Left by Sandeep Chakradhari on Jan 15, 2010 1:04 AM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
The crust is similar to that of an English muffin, and the pizza is often cooked in an electric oven.It is usually cut with scissors or a knife and sold by weight.
Regards,
Custom Remodelers Circle Pines
Left by hujikol on Apr 18, 2010 9:46 AM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
I am looking forward for your next post on this topic.Your posts really do the help.Thank you! http://www.trustsneakers.com men’s shox dream to you!
Left by good product on Jun 11, 2010 2:58 AM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
Tracking sometimes very tricky and I believed it could be a difficult task sometimes if you are using complex applications.I am working on image management software and I can understand its really tough.
Left by Smithfiona on Oct 21, 2010 2:23 AM

# Missing Tracking Data problem followup from previous post
Requesting Gravatar...
If you find any other tibia gold shop faster than us ,please let us know and we will beat them!
Left by garage shoes on Jun 26, 2011 10:06 PM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
thanks for sharing this content Cartoon hd </>
Left by james on Mar 26, 2017 9:36 PM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
nice post Cartoon hd
Left by james on Mar 26, 2017 9:38 PM

# re: Missing Tracking Data problem followup from previous post
Requesting Gravatar...
Shop hoa tươi đẹp giá rẻ truy cập vào http://hoatuoishop.net/
Shop Hoa Tươi. Điện hoa đẹp TPHCM. Dịch vụ hoa tuoi giá rẻ
Dịch vụ hoa tươi đẹp – điện hoa tươi online tận nơi miễn phí . Shop hoa TPHCM
Left by hung on Apr 20, 2017 11:25 PM

Your comment:
 (will show your gravatar)


Copyright © Michael Stephenson | Powered by: GeeksWithBlogs.net