Biztalk Receive location and Send ports creating too many sleeping sessions while using notification, XMLPolling and Polling RRS feed

  • Question

  • Problem Statement:
    There are many [many times 250+] sleeping sessions in DB for BizTalk service user within our custom SQL DB.
    BizTalk Receive location[s] uses Notification service for processing message in some table. 
    We process some 30k To 35k messages per hour and applications heavily uses Notification, Polling and Xml Polling.
    I was trying to understand, how those sessions are created and when would they be deleted in SQL Server. 

    I tried few tests to see number of sleeping sessions in SQL Server after inserts in the tables but unable to conclude with confidence. 

    Any Idea how Biztalk server's SQL adapter talks to the DB and manages sessions if Notificaiton/XmlPolling/Polling used?

    I did read MSDN docs but they don’t tell in details how notification services will create and handle sessions behind the scene for BizTalk database interaction.

    Thank you in advance!


    Regards, Nilesh Sarkate [Please remember to click "mark as answered" when you get a correct reply to your question]

    Wednesday, January 15, 2020 6:03 PM

All replies

  • Which version of BizTalk, what CU level?
    Wednesday, January 15, 2020 8:36 PM
  • Thank you for reply... its BizTalk 2016 and CU6.

    This is observed same with CU5 too.

    Wednesday, January 15, 2020 10:07 PM
  • Can you reproduce this in a test environment?

    If so, can you try disabling the notifications and polling separately, to see which is the issue.

    Note: There was an issue with connection leaks that was supposedly fixed, FIX: WCF-SQL adapter fails intermittently if AmbientTransaction is True or if ReceiveTimeout is not more than polling time plus time to query data from SQL Server.  but as per this BizTalk 2013R2 & SQL SERVER - Timeout Errors caused by Connection Leaks it appears it might still occur in some scenarios.   So changing the ReceiveTimeout to a really large number may help.

    Friday, January 17, 2020 12:26 AM
  • Thank you, Colin, for reply!

    Yes, I did reproduce it on lower environment too with few tests. 

    I tried first with Notificaiton, XMLPolling and then Polling. Interestingly notification is the one who hogs onto the sleeping sessions & my solution uses notification services. 

    Secondly, I've gone through that fix you mentioned in  reply; I feel that was the issue with BizTalk 2013 and gotten fixed[?]. Also I am using BizTalk 2016 Server. 

    Thing is I have some 175 Send ports and some 27 Receive locations. 

    I've observed that, the moment I start any single receive location, I saw three sessions. One suspended and two sleeping immediately. At this moment no data was available to be processed. Sleeping sessions were seen there for long time at-least more than 10 mins. 

    When I processed data repeatedly, [as its notification service, and I processed somewhat 20 records] it created 12 sleeping sessions which I observed. 

    Now question was when my system goes through nightly batch processing where it processes very high volume, this number shoots up very dramatically. one time my DBA reported 480 sleeping sessions. 

    Now, my question was what is the rational by which SQL server is creating those many sessions and when would they be deleted from SQL Server.

    I played with ReceiveTimeout but looked that is not respected by SQL Server.

    So I am wondering if my system processes some 40k-50k messages/hour then I got to be prepared for that situation by controlling total number of sessions to be created by notification/XMLPolling/Polling.



    Friday, January 17, 2020 1:37 AM
  • Hi Nilesh

    It does not surprise me that it is the notifications that require the sleeping sessions, as how else would the database notify BizTalk that an event has happened?  It needs that sleeping session to be able to send that notification.  It is not like a webhook where you make the call and give it an address for the callback, and the connection can be dropped meanwhile.

    Are these sleeping sessions actually causing an issue?  If not, then I wouldn't worry about it.

    If it is an issue, is it possible to make the notifications more generic, just to let BizTalk know there is something to pick up from an special "events" table, which would then kick of the appropriate workflow?

    Sunday, January 19, 2020 7:02 PM
  • How do the MaxPoolConnectionSize values looks on those ports? Maybe the would account for the lingering sessions.

    edit: Also on the port override the "WorkStationId" value  to make it easy to distinguish which port is which if using sp_who2.

    • Edited by T.Stevens Thursday, January 23, 2020 10:47 PM
    Thursday, January 23, 2020 10:44 PM