CODEWITHSUNDEEP
azureazure-sql-databasebiztalkbiztalk-2013r2
Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Biztalk WCF-Custom Adapter gives time out error

I am running a stored procedure on SQL Azure Database using BizTalk WCF-Custom adapter. I have around 20k records to be processed as composite operation. We are using BizTalk 2013 R2 on Windows Server 2012.

I am getting following error after 40-50 minutes:

A message sent to adapter "WCF-Custom" on send port "Swire.BizTalk.M3.Send.PushAssets.Local" with URI "mssql://xngoo0zsw2.database.windows.net//overvuuat_20150720?" is suspended. Error details: Microsoft.ServiceModel.Channels.Common.InvalidUriException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Adapters.Sql.SqlAdapterConnection.OpenConnection() --- End of inner exception stack trace ---

Server stack trace: at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result) at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result) at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result) MessageId: {9034380A-E116-4694-BD70-F9933BF37BD3} InstanceID: {91B6E1AA-32AD-48D9-A18A-F9BF20529764}

Following are configurations for that send port:

enter image description here

I increased the timeout from 00:50:00 to 05:00:00 but no change in result. still getting same error.

I run SP_WHO2 to get the list of connection those are running/queued. There were more than 100+ connection when the error raised. One of the query "Select Into" was in suspend mode and displayed as it was running from SSMS. but we have no such query and we never run any query from directly from SSMS.

Please suggest a way to resolve this.

Upvotes: 0

Views: 1423

Answers (1)

Satya_MSFT
Satya_MSFT

Reputation: 1022

Looks like you are hitting the maxConnectionPoolSize which is 100 and increasing the timeout wouldn't help in this case. You can use non-pooled connections but before going that see why there are 100+ connections to the database. This could be because of the performance issues on the database (may need to scale) / application issues (locks being held on the table and blocking other queries or connections aren't closed so none of them are returned back to pool).

For performance issues, query sys.resource_stats to see whether your database resource utilization is peaking. Also you can use sys.dm_Exec_Requests and sys.dm_tran_locks to see what other connections are doing. I believe you are closing the connections in your app when you are done using them (otherwise they won't return back to pool)

Upvotes: 1

Related Questions