HELP! BizTalk SQL Adapater Hanging, and Hanging, and Hanging…

 Hey Folks,

Have a BizTalk solution deployed at a customer site and once every four weeks (or so), BizTalk generates the following event:

“The adapter “SQL” raised an error message.  Details “A connection with the transaction manager was lost. (Exception HRESULT: 0x8004D01C)”.

This error is originating from a Sequential Convoy Orchestration that runs 100’s of times per day.  What is strange about this error is the frequency (or lack of).  It always seems to occur at a point where the orchestration is using the SQL adapter to call a simple stored procedure.

What stinks about this error is that it causes the Sequential Convoy Orchestration to pause.  It takes a restart of the BizTalk host to get the orchestration to un-pause and pick up where it left off.  What is cook about this problem is that once BizTalk is restarted, it is able to finish the stored procedure call and continue like nothing ever happend.