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.
RSS Feed
Henk Kelder said
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?
Victor Fehlberg said
I’d start here:
http://geekswithblogs.net/gwiele/archive/2004/11/25/15974.aspx
HashName said
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.
Fehlberg Victor said
Thanks for sharing.