BizTalk : How To : Trouble Shoot BAM EventBus Service Error in Event Log

post by Tiho

Here are 8 tables in each BizTalk Message Box database which store the tracking data before it is imported by the BAM EventBus Service (a.k.a. TDDS or Tracking Data Decoding Service) into the DTA/HAT and the BAM Primary Import databases. The tables are named TrackingData_x_y where 0 ≤ x ≤ 1 and 0 ≤ y ≤ 3. It is absolutely critical that the sizes of these tables do not increase over time. If the sizes are increasing than either TDDS is not able to catch up with the load or it is not working properly. There is a performance counter that can be used to monitor the size of the tracking data – BizTalk:Messsage Box:General Counters\Tracking Data Size.

The first step to troubleshoot issues with the tracking data is to examine the Windows Event Log on the BizTalk Tracking Host machine for errors from the BAM EventBus Service. Error information along with the serialized original tracking data might be available in the TDDS_FailedTrackingData tables in the DTA/HAT and the BAM Primary Import databases. Another place where error information might be available is the TDDS_Heartbeats table in the BizTalk Management database.

In this post I will focus on one very common cause which manifests as an error from the BAM EventBus Service in the Windows Event Log with event ID 25. 

The error should look like this:

Event Type: Error
Event Source: BAM EventBus Service
Event Category: None
Event ID: 25
Date: 12/20/2006
Time: 10:38:27 AM
User: N/A
Computer: TRACKINGHOST
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: DBSERVER, Database: BAMPrimaryImport.

This error will usually show up every 5 minutes or so. Below are the steps to resolve it.

First, make sure that you are not hitting any of the issues described in this article. If this is the case, then killing the orphaned sessions or restarting the SQL Server machine will resolve the issue. You can use the information in this article to identify orphaned sessions.

If the issue is still there, then you most certainly have permissions problems. The account under which TDDS (and the BizTalk Tracking Host) is running must have execute permissions for the following stored procedures in the BizTalk Message Box database: TDDS_RedisterTDDSAccess and TDDS_GetNumTrackingPartitions. The same account must also have execute permissions for the TDDS_Lock stored procedure in the DTA/HAT and the BAM Primary Import databases.

The permissions are set correctly when BizTalk Server is first installed but might be altered later directly or indirectly by manually setting explicit deny permissions for the specific account or any group that it is a member of.

The best way to check that permissions are indeed the problem is to open Task Manager on the Tracking Host machine and on the Processes tab check the account under which the BTSNTSvc.exe process is running. This is the Tracking Host account. Now start SQL Query Analyzer or SQL Management Studio (or any of the command line counterparts) with the same user credentials as BTSNTSvc.exe. You can use the “runas” command or right-click on a shortcut and choose “Run as…”. I suggest that you do this test on the Tracking Host machine to rule out any network connectivity issues.

After you have connected to the correct SQL database execute the following:

In all BizTalk Message Box databases:

DECLARE @RC int
DECLARE @retVal int
EXEC @RC = [BizTalkMsgBoxDb].[dbo].[TDDS_RegisterTDDSAccess] @retVal OUTPUT 
SELECT @RC
GO

DECLARE @RC int
DECLARE @nPartitions tinyint
EXEC @RC = [BizTalkMsgBoxDb].[dbo].[TDDS_GetNumTrackingPartitions] @nPartitions OUTPUT 
SELECT @RC
GO

In the BizTalk DTA and BAM Primary Import databases:

DECLARE @RC int
DECLARE @resource nvarchar(128)
DECLARE @milisecTimeout int
DECLARE @retVal int
SELECT @resource = N'Foo'
SELECT @milisecTimeout = 5000
EXEC @RC = [BAMPrimaryImport].[dbo].[TDDS_Lock] @resource, @milisecTimeout, @retVal OUTPUT 
SELECT @retVal
SELECT @RC
GO

The results of these queries should be 0 and there should not be any errors. If you get “EXECUTE permission denied” errors then grant the corresponding execute permissions until you can execute the queries without any errors. At this point TDDS should start moving the tracking data from the Message Box databases to the DTA and the BAM databases.