Errors during BizTalk TDDS Processing

  • Article

The TDDS service is responsible for moving the Tracking Data from the BizTalkMsgBoxDb to the BizTalkDTADb and the BAM data to the BAMPrimaryImport database. Now, TDDS runs within the Tracking Host (if you open up BizTalk Admin Console and go to Platform Settings -> Hosts, the ‘Tracking’ column shows whether a Host is configured for tracking).
Sometimes, due to incorrect maintenance of the BizTalk databases, TDDS may run into issues in moving this data. I recently came across one such problem.

On Eventviewer, we were seeing this error –
Event Type: Error
Event Source: BAM EventBus Service
Event Category: None
Event ID: 25
Date: 5/16/2013
Time: 4:48:36 AM
User: N/A
Computer: ABC
Description:
Either another TDDS is processing the same data or there is an orphaned session in SQL server holding TDDS lock.Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. SQLServer: MyServer\Instance, Database: BizTalkDTADb.  

So, the first thing to check here would be to look up all the SPIDs on SQL Server, and find if any of the BizTalk SPIDs are blocked (look in the ‘BlkBy’ column). You can run this on the ‘master’ database to get this information – sp_who2

You may notice SPIDs being blocked by other SPIDs from the above output. But killing SPIDs explicitly maybe risky. So, we went ahead and enabled TDDS logging in the BTSNTSvc.exe config file (you have to edit BtsNtSvc64.exe.config if the tracking host is 64 bit), by adding this section to it –

<system.diagnostics>
<switches>
<add name="Microsoft.BizTalk.Bam.EventBus" value="1" />
</switches>
<trace autoflush="true" indentsize="4">
<listeners>
<add name="Text" type="System.Diagnostics.TextWriterTraceListener"initializeData="c:\tdds.log"/>
</listeners> 
</trace>
</system.diagnostics>  

This writes out the TDDS traces in the file mentioned in the config section above (a restart of the Tracking Host is required). We noticed this in the log –
<BizTalk2006> ... Exception information: "Time out occurred while executing the event data. This is a non critical error and the BAM Event bus service will try resubmitting the data again." SQLServer: VSQLPCN, Database: BizTalkDTADb.Stored Procedure dtasp_UpdateMessageEvent failed to run.Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.  

So, quite clearly we see that TDDS was timing out while moving the data to the BizTalkDTADb. This may often happen due to large table sizes. So, we looked into the table-sizes of the DTADb. Some of the tables were really large. At this stage, we decided to Purge the BizTalkDTADb as the data was not that important in this particular instance. There are two ways of doing this –

• Method 1 – this removes only completed instances from the BizTalkDTADb database
1.Back up all BizTalk Server databases.
2.Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the dtasp_PurgeAllCompletedTrackingData stored procedure, visit the following MSDN website:
https://msdn.microsoft.com/en-us/library/aa561918.aspx
Note : This action deletes all completed messages.

Couple of alerts/disadvantages associated with this method –

  1. Performing this procedure deletes all tracking data for completed instances from the BizTalk Tracking (BizTalkDTADb) database regardless of completion time. Before performing this procedure, you should archive the BizTalk Tracking (BizTalkDTADb) database separately from the other BizTalk Server databases.
  2. If you have very large data in the BizTalkDTADb, this operation is going to take a very long time. Internally, this Stored Proc. creates temp tables, and lots of JOINs come into play as well. Also, this is a logged operation – this means your transaction log will grow – in proportion of your existing data. So, you need more log space, you are taking more time and you can lock a lot of stuff.

So, exercise this option when you absolutely want non-completed instances to be continued for tracking in the BizTalkDTADb.

• Method 2 – this removes all data from the BizTalkDTADb database!

1.Back up all BizTalk databases.
2.Execute the dtasp_CleanHMData stored procedure. Only use this option if the BizTalkDTADb database contains many incomplete instances that must be removed.

To do this, follow these steps:
a. If you use HTTP or the SOAP adapter, stop the IIS service. Stop all BizTalk hosts, services, and custom isolated adapters.
b. Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
c. Restart all hosts and BizTalk Server services. Now restart the IIS service.

Note : You can also perform Method 2 mentioned above very easily using the BizTalk Terminator Tool. (https://www.microsoft.com/en-us/download/details.aspx?id=2846 )

After you connect to your BizTalk Group using Terminator, you need to click on the ‘Delete’ button at the bottom, and select ‘PURGE Everything in the DTA 2006 or Above’ option. Now go to the ‘Parameters’ tab and click ‘Execute’. Please note while running the Terminator, you need to stop IIS, the BizTalk Services and the SQL Server Agent as before.

Once we deleted the messages from the DTADb and restarted the Tracking Host, the Tracking Data started moving to DTADb once again. Issue resolved! We cannot emphasize the importance of configuring and running the out of the box Biztalk SQL Agent Jobs (https://support.microsoft.com/kb/919776).
This problem could easily have been averted if the DTA Purge and Archive job was running properly in that environment. Since, that job was not running in that environment for a while, backlog of data occurred in the DTADb, and things went on a downward spiral from that point.

Written by
Arindam Paul Roy

Reviewed by
Chirag Pavecha & Shailesh Agre

Microsoft India GTSC