[BizTalk] BTS stop responding and you got TDDS lock timeout error in event log

My BTS 2006 stop processing incoming messages with the following event log entry.

Event Type:    Error
Event Source:    BAM EventBus Service
Event Category:    None
Event ID:    25
Date:        7/10/2008
Time:        5:51:49 PM
User:        N/A
Computer:    [BTSSVC]

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: [SQLSVRNAME], 
Database: BAMPrimaryImport.


For more information
, see Help and Support Center at 
http://go.microsoft.com/fwlink/events.asp.

If you are running BTS 2004, then simply following this KB article (Microsoft KB897653).

If you are running BTS 2006, TIHO's blog has detail information about this error and how to fix it.

Simply speaking, if this happens on BTS2006 server, then one of the following must be solved:

1. There are orphanded sessions.
   
   
   
   1. Orphanded sessions are those clients that is not able to free their network connection when terminated.
   
   
   
   2. This article shows how to identify if you are running into orphanded session problems and how to solve it.
   
   
   
   



2. You have permission issues
   
   
   
   1. The account under which TDDS(Tracking Data Decode Service) must have execute permissions for the following stored procedures in BTS Msg db:TDDS_RedisterTDDSAccess & TDDS_GetNumTrackingPartitions.
   
   
   
   2. The same account must also have execute permission to TDDS_Lock stored procedure in DTA/HAT & BAM Primary Import Databases.
      
      
      
      
      
      1. To check and solve permission issues, first connect to BTS Msg DB with same credentials TDDS is running under. then try to execute the following sql statement:
         

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

         
         
      
      
      
      
      
      1. 
         

         And the following against BTS DTA & BAM Primary Import databases:

         
         
         

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

         
         
      
      
      
      
   
   
   
   

If you got error messages state you are lack of permissions executing those sql statement against BTS MsgDB & DTA/BAM DBs, try to grant execute permission to the account you are connecting to SQL server until no more error messages are shown.