none
WCF-SQL Adapter and idle SQL Server sessions RRS feed

  • Question

  • Hi,

    I have a BizTalk 2013 server with a send port that periodically send SQL statements to a remote SQL Server. The remote SQL Server hosts the database of a Dynamics NAV server.

    The DBA of this SQL Server warned me that a lot of SQL Server sessions remain idle after a long period of time (about 20 idle sessions coming from my BizTalk server with a Idle time that is more than 10 days). He thinks that we have a trouble in BizTalk to close the connections to the SQL Server.

    I wondered if I can handle the situation by changing some settings in the sqlBinding used by my send port, for example the MaxPoolSize or other parameters.

    Any idea?

    Thanks.

    Wednesday, July 30, 2014 7:20 AM

Answers

  • So, is this causing an actual problem or is this something the DBA just happened to see?

    20 idle sessions isn't a lot at all and is well below the default Connection Pool size, 100 in most cases.

    So long as it does not keep increasing unchecked, I'd say what you're seeing is the expected and desired behavior.

    You maybe have had a burst at some point causing 20 connections, then normal continuous operations only uses 1 or 2.

    Wednesday, July 30, 2014 11:48 AM
  • When we saw 20+ idle sessions on SQL Server, the situation in BizTalk is completely clean, no suspended instances and no running instances.

    Which is still the behavior I would expect from the Connection Pool since it is used by the WCF SQL Adapter.

    This would only be a problem if the count kept increasing unrestrained.

    Wednesday, July 30, 2014 1:55 PM

All replies

  • Hi David,

    SQL always use to give a acknowledgment while you have any CRUD Operation against it . So have you debugged your Orchestration and verify that existing Orchestration are not getting suspended in wait of response .

    Thanks

    Abhishek

    Wednesday, July 30, 2014 11:29 AM
  • So, is this causing an actual problem or is this something the DBA just happened to see?

    20 idle sessions isn't a lot at all and is well below the default Connection Pool size, 100 in most cases.

    So long as it does not keep increasing unchecked, I'd say what you're seeing is the expected and desired behavior.

    You maybe have had a burst at some point causing 20 connections, then normal continuous operations only uses 1 or 2.

    Wednesday, July 30, 2014 11:48 AM
  • Hi Abhishek,

    I don't have any orchestrations suspended waiting for a response.

    When we saw 20+ idle sessions on SQL Server, the situation in BizTalk is completely clean, no suspended instances and no running instances.

    Thanks.

    Wednesday, July 30, 2014 12:03 PM
  • When we saw 20+ idle sessions on SQL Server, the situation in BizTalk is completely clean, no suspended instances and no running instances.

    Which is still the behavior I would expect from the Connection Pool since it is used by the WCF SQL Adapter.

    This would only be a problem if the count kept increasing unrestrained.

    Wednesday, July 30, 2014 1:55 PM