WCF-Custom" raised an error message. Details "System.Data.SqlClient.SqlException: Timeout expired

Question

What is this message. I have a stored procedure that looks if there is any work to do.
I use the NOLOCK hint to see if work should be done. I do not use the Ambient Transaction.

The sproc looks something like

Select @counter=count(*) from sometable with (NOLOCK) where condition = NEVERTRUE
If @counter > 0
BEGIN
   DO SOME WORK !
END

So since there is never any data found the adapter should never give a timeout.
When I start this receive port i have the default Timeout set to 10 minutes. And surely after 10 minutes I see this message in the eventlog....

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5740
Date:  14-12-2010
Time:  10:47:07
User:  N/A
Computer: BA34T
Description:
The adapter "WCF-Custom" raised an error message. Details "System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at Microsoft.ServiceModel.Channels.Common.Design.AdapterAsyncResult.End()
   at Microsoft.ServiceModel.Channels.Common.Channels.AdapterInputChannel.EndTryReceive(IAsyncResult result, Message& message)
   at System.ServiceModel.Dispatcher.InputChannelBinder.EndTryReceive(IAsyncResult result, RequestContext& requestContext)
   at System.ServiceModel.Dispatcher.ErrorHandlingReceiver.EndTryReceive(IAsyncResult result, RequestContext& requestContext)".

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

I installed the latest cumulative update for the adapter and I really don't know what is going wrong here.
Microsoft is deprecating the Old SQL Adapter but currently I have more issues with the WCF SQL Adapter than with the classic SQL adapter.

Is there anybody who recognizes this behaviour and could help me a bit to get rid of this failure.

The problem is that something is locked in the DB and once this occurs i have some blocking in the target database with the receive location as reason of the blocking

Please help !

 

 

 

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 1

can you tell me what is the Transaction Isolation Level and Transaction Timeout you have set

refer this link for more information

http://msdn.microsoft.com/en-US/library/dd788041(v=BTS.10).aspx

-D

Answer 2

I have not set any transaction level or timeout. So its all back tyo the defaults.

I can sse with the profiler that I am using transaction level read committed.

 

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 3

If your query is wrapped in a MSDTC transaction then the default maximum time for a MSDTC transaction set in the machine.config file is ten minutes. I think you have three options ; 1) tune your query to run in less than ten minutes 2)configure the SQL adapter so you do not use MSDTC(I don't know how you do this but maybe there is some configuration setting that does this for you    o r 3)Change the setting for the maximum MSDTC time out in the configuration file.

The think the 1) is the most preferrable option then 2) and then 3). please note if change 3 you change it for all MSDTC transactions.

Below is the taken from http://msdn.microsoft.com/en-us/library/dd788435(BTS.10).aspx

For BizTalk Server 2006 R2

1. Install the hotfix available for BizTalk Server 2006 R2. The hotfix is available from http://go.microsoft.com/fwlink/?LinkID=106011. This hotfix fixes an issue with the WCF adapter where the adapter has a maximum timeout of 60 minutes. This hotfix ensures that the WCF adapter reads the timeout settings from the machine.config.
   
2. Specify the timeout for the WCF adapter in the machine.config. Navigate to the machine.config file under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG and add the excerpt that resembles the following.
   
   
   Copy

   <configuration>
    <system.transactions>
    <machineSettings maxTimeout="02:00:00" />
    </system.transactions>
   </configuration>
   

   With this setting, the WCF adapter timeout is set to 2 hours.
   
3. Specify the timeout settings for MSDTC transactions in the machine.config. Navigate to the machine.config file under <system drive>:\WINDOWS\Microsoft.NET\Framework\<version>\CONFIG and add the excerpt that resembles the following.
   
   
   Copy

   <system.transactions> 
       <defaultSettings distributedTransactionManagerName="MACHINE_NAME" timeout="02:00:00"/> 
     </system.transactions>
   
   

   With this setting, the MSDTC timeout is set to 2 hours. The default value for MSDTC timeout is 10 minutes.
   

Answer 4

I have UseAmbientTransaction set to false.....

Doesn't that switch off all MSDTC stuff ?

---

Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 5

Well I got some extra information.

At first my stored procedure was something like :

Procname was GetRecords.

Set rowcount = 100
Update sometable set somefield='PROCESSED' where somefield = 'NEEDSPROCESSING'

and then i would use inserted to figure out what records i had to oprocess.

Now i changed it to

Procname = SeeIfRecordsAvailable

select @recordsFound=count(*) from sometable with (Nolock) where somefield='NEEDSPROCESSING'
if @RecordsFound > 0
begin
     EXEC GetRecords
end

And now i am no longer seeing the strange warnings in the eventlog. There is probably some bugf in the WCF adapters that will happen if you start a transaction like the update did, but there is still no data.

By changing the mechanism to only start a sql-transaction when there is data i got rid of this nasty error message. A side effect of this is that a lot less deadlock situations occur and everything is running smootly now.

Strange though that this change was needed. In my opinion a serious bug in the WCF adapter !.

 I made a blog posting about my findings, you can find it here : http://bloggingabout.net/blogs/wellink/archive/2010/12/22/getting-rid-of-wcf-custom-quot-raised-an-error-message-details-quot-system-data-sqlclient-sqlexception-timeout-expired.aspx

 Well0549, Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread

Answer 6

Despite the blog post by Well I cannot seem to get rid of this. However, on the test server I'm not experiencing this, only on production. 

My test server is a single server with SQL 2008R2 and BT 2010 installed whereas production has a dedicated SQL server and BT server (same versions).

Have there been any updates on this matter?