Home > BizTalk Server > BizTalk Server 2006 SQL Adapter Lock Resolution

BizTalk Server 2006 SQL Adapter Lock Resolution

We just fixed a rather hairy problem here at work that I’m sure will benefit someone out there. Here’s a brief description:

Using BizTalk Server 2006 (R1) we had a problem with a particular application that used the SQL Server Adapter for polling. It would run a very simple stored procedure every 30 seconds. The stored procedure basically queried a table and then truncated it. The problem we were facing was that the SQL Server DBA was finding the database table exclusively locked after some amount of time (usually within a minute or so). We tried a bunch of stuff to no avail and then decided to call Microsoft.

The Microsoft gentleman tried a few things here and there, but the problem persisted until he called back with a new idea. We had 2 Receive Handlers associated with the SQL Adapter.  However, only one was in use and so I was asked to delete the second Receive Handler. I reluctantly agreed after realizing that no one else was using that Receive Handler. Well, guess what – that fixed the issue. I wish I could have shared some obscure registry key that we flipped or something, but no. I guess the moral of the story is to only use a single Receive Handler for your SQL Adapter.

Advertisements
Categories: BizTalk Server
  1. Henk Kelder
    February 27, 2008 at 10:16 pm

    We seem to suffer from the same problemen. However, we use the SQL adapter for polling on two receive locations and also for adding rows to various tables. Things we cannot switch off or remove…

    Any hints?

  2. June 4, 2008 at 9:20 am
  3. HashName
    April 22, 2009 at 8:52 pm

    We also faced lots of issues with SQL Adapter, with the SERIALIZABLE Isolation level. We eliminated that by setting the isolation level to read-committed explicitly.
    Another problem was because of Distributed Transactions. Because of this rolling back becomes tricky. In some cases, especially when the XML being generated by SP is malformed, it results in an uncommitable txn which cannot be handled through try-catch or rollbacks.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: