TDDS is not moving data from msgbox to destination DB (DTA or BAM)
Issue Definition:
TDDS is not moving data from msgbox to destination DB (DTA or BAM)
Troubleshooting:
Check the following:
1. Check to make sure tracking host is running
2. Check to make sure the min seqnum value in trackingdata_x_x is greater than the corresponding lastseqnum value in the tddstreamstatus table (since any seqnum value less than lastseqnum will never been seen by TDDS)
3. Check to make sure the gap between min value in trackingdata_x_x and corresponding lastseqnum value is not too great (in the past, TDDS has had some issues when min value is hundreds or especially thousands greater than lastseqnum. In a perfectly healthy environment should just be 1 or 2 greater.)
4. Check eventlogs for new errors from BAM Eventbus Service
5. Check tdds_failedtrackingdata table in destination DB for new errors
6. Check TDDS and BTS traces for errors
CASE 1:
When you observe that for all but 1 trackingdata_x_x table, the min seqnum and lastseqnum were not changing at all. This means TDDS is unable to process the next record in the table. It also implies TDDS is unable to fail out (to tdds_failedtrackingdata table) the next record in the table since that also increments seqnum.
I also noticed that we were only getting the “parameter is invalid” error every few minutes. TDDS should process thousands of events per minute so getting a “parameter is invalid” error every few minutes should not completely block TDDS.
Based on the above, we would want to consider other possible explanations like:
- SQL blocking in destination DB which was preventing TDDS from inserting the row into destination table or tdds_failedtrackingdata table
- SQL blocking in msgbox which was preventing TDDS from reading the next row in or deleting the next row from trackingdata_x_x
- TDDS process hang
- TDDS process crash
When this is combined by the tracking host services had been restarting frequently, the event logs revels the following.
Log Name: Application
Source: Application Error
Date: 8/10/2016 8:52:04 AM
Event ID: 1000
Task Category: Application Crashing Events
Level: Error
Keywords: Classic
User: N/A
Computer: abcd.com
Description:
Faulting application name: BTSNTSvc64.exe, version: 3.9.469.0, time stamp: 0x4c548eb4
Faulting module name: mscorlib.ni.dll, version: 4.0.30319.34209, time stamp: 0x53489fcf
Exception code: 0xc00000fd
Fault offset: 0x00000000004b66f3
Faulting process id: 0x61b0
Faulting application start time: 0x01d1f31f2144fbbb
Faulting application path: D:\Program Files\Microsoft BizTalk Server 2010\BTSNTSvc64.exe
Faulting module path: C:\Windows\assembly\NativeImages_v4.0.30319_64\mscorlib\701f2b79b02a02beba70e50bb2edb212\mscorlib.ni.dll
Report Id: 61a17095-5f12-11e6-be7f-0017a4779450
Faulting package full name: %14
Faulting package-relative application ID: %15
Exception code indicates a stack overflow which requires dump analysis for troubleshooting.
CASE 2:
Tracking data will go to the MsgBox tracking tables first and then will get moved the DTA DB tracking tables. The movement between these tables is managed by keeping track of the sequence numbers in the MsgBox and tracking tables.
Looks like these sequence numbers are out of sync in your environment.
Tracking service TDDS starts at lastSeqNum from the TDDS_StreamStatus table and counts up – In your case, it will never see those rows in TrackingData_x tables in the MsgBox DB. This is why the data is not getting moved to the tracking tables in the DTA DB and hence the tracking information is not showing up in the admin console.
Findings in the BHM report
This is what we have in the tracking tables in the 3 msgBox :
| Total rows in MsgBox TrackingData table(s) - MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb" on BPDBMMBWMCL-DB | 66 |
| Total rows in MsgBox TrackingData table(s) - MSGBOX DB 2 "BizTalkMsgBoxDb" on BPDBSMBWMCL-DB | 1307 |
| Total rows in MsgBox TrackingData table(s) - MSGBOX DB 3 "BizTalkSecondryMsgBoxDb" on BPDBMMBWMCL-DB | 1350 |
This is what we have in the TDDS StreamStatus Table of DTA.
Look at the LastSeqNum : they are large
BizTalk - DTA Db : TDDS StreamStatus Table
| serverName | destinationId | partitionId | lastSeqNum | eventIdx |
| BPDBMMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 0 | 1575261 | NULL |
| BPDBMMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 1 | 1555544 | NULL |
| BPDBMMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 2 | 1567448 | NULL |
| BPDBMMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 3 | 1566151 | NULL |
| BPDBMMBWMCL-DB\BizTalkSecondryMsgBoxDb\1 | 1 | 0 | 1675396 | NULL |
| BPDBMMBWMCL-DB\BizTalkSecondryMsgBoxDb\1 | 1 | 1 | 1654160 | NULL |
| BPDBMMBWMCL-DB\BizTalkSecondryMsgBoxDb\1 | 1 | 2 | 1634811 | NULL |
| BPDBMMBWMCL-DB\BizTalkSecondryMsgBoxDb\1 | 1 | 3 | 1661236 | NULL |
| BPDBSMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 0 | 1588245 | NULL |
| BPDBSMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 1 | 1584565 | NULL |
| BPDBSMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 2 | 1555553 | NULL |
| BPDBSMBWMCL-DB\BizTalkMsgBoxDb\1 | 1 | 3 | 1577779 | NULL |
Now look at the TrackingData tables in the 3 MsgBoxes
In the 3 MsgBoxes we have Min SeqNum well under the LatSeqNumber of the table above so these TrackingData will never be moved to the DTA
BizTalk - All MsgBox Dbs : Tracking Tables Sequence Numbers
MSGBOX DB 1 (MASTER) "BizTalkMsgBoxDb" on BPDBMMBWMCL-DB
MSGBOX DB 2 "BizTalkMsgBoxDb" on BPDBSMBWMCL-DB
| Tracking Table | Row Count | MIN seqNum | MAX seqNum |
| TrackingData_0_0 | 0 | NULL | NULL |
| TrackingData_0_1 | 0 | NULL | NULL |
| TrackingData_0_2 | 0 | NULL | NULL |
| TrackingData_0_3 | 0 | NULL | NULL |
| TrackingData_1_0 | 330 | 296588 | 296917 |
| TrackingData_1_1 | 331 | 296817 | 297147 |
| TrackingData_1_2 | 338 | 296657 | 296994 |
| TrackingData_1_3 | 308 | 296673 | 296980 |
| 8 Rows |
MSGBOX DB 3 "BizTalkSecondryMsgBoxDb" on BPDBMMBWMCL-DB
| Tracking Table | Row Count | MIN seqNum | MAX seqNum |
| TrackingData_0_0 | 0 | NULL | NULL |
| TrackingData_0_1 | 0 | NULL | NULL |
| TrackingData_0_2 | 0 | NULL | NULL |
| TrackingData_0_3 | 0 | NULL | NULL |
| TrackingData_1_0 | 305 | 307677 | 307981 |
| TrackingData_1_1 | 377 | 316880 | 317256 |
| TrackingData_1_2 | 338 | 307042 | 307379 |
| TrackingData_1_3 | 330 | 306869 | 307199 |
There are two approaches that you can follow to fix this issue:
First Approach: Run terminator to fix sequence number gaps – this will need deleting tracking data from the MsgBox DB
BizTalk terminator tool provides a task to reset the sequence number gaps. It will purge the tracking related data from the MsgBox DB as well. You will lose all the previous BizTalk tracking related data.
Below are the steps to follow in case you want to follow this approach:
You can download the terminator tool from the below link:
https://www.microsoft.com/en-us/download/details.aspx?id=2846
You will have to make sure you have the below pre-requisites in place before you run the tool:
· Admin privileges on the BizTalk machine. SysAdmin privileges on the SQL DBs and BizTalk administrative privileges.
· Stop all BizTalk services, SSO service and IIS service on all the BizTalk server nodes.
· Take backup of the BizTalk databases.
· Stop the SQL server BizTalk jobs or the SQL server agent if that is possible.
Please follow the below steps to perform the terminator tasks required:
· Open terminator and provide the required information. Hit connect
· Click on “Delete” button and select the “Purge TrackingData Tables in MsgBox” task from the drop down.
· Provide the below specified parameters – change the DB server name and DB name according to your environment.
· Click on execute to run this task.
· This will delete tracking tables of the MsgBox DB and reset the sequence numbers.
· Restart the tracking service after this. Things should work fine now.
Second Approach – Change the sequence number manually – do not have to purge MsgBox tracking tables.
You can follow this approach in case you do not want to purge MsgBox tracking tables. You can change the sequence number values manually. You will still have to make sure you take the complete downtime before doing this change.
You will have to make sure you have the below pre-requisites in place before you run the tool:
· Admin privileges on the BizTalk machine. SysAdmin privileges on the SQL DBs and BizTalk administrative privileges.
· Stop all BizTalk services, SSO service and IIS service on all the BizTalk server nodes.
· Take backup of the BizTalk databases.
· Stop the SQL server BizTalk jobs or the SQL server agent if that is possible.
You have to find the minimum sequence number for each tracking tables once the above is in place. You will have to run the below query for these four tables in the MsgBox DB – TrackingData_1_0, TrackingData_1_1, TrackingData_1_2, TrackingData_1_3.
SELECT TOP 1 SeqNum FROM[BizTalkMsgBoxDb].[dbo].[TrackingData_1_0]order by SeqNum asc
You have to keep note of the number for the individual tables. You will have to update the DTA DB TDDS_StreamStatus table once you have above information. You will have something like this below in your environment - below rows: (below screenshot is for example)
The lastSeqNum column needs to be changed to the value which is one less than the value you got from the above query. The value for 1_0 table should go to partitionId = 0 column. Value for 1_1 table should go to partitionId = 1 column.
Note:
Make sure we have only one dedicated tracking host, as having multiple tracking host is prone to cause Seqnum issue
Written By
Swarna Prabhu
Microsoft GTSC