none
SQL Server 2016 ODBC Connection Errors Occurring Intermittently: Need assistance with a solution

    Question

  • We have an application in a WS 2016 connecting to a SQL 2016 Server through ODBC Driver 11 for SQL Server version 2014.120.5543.11.  We are experiencing intermittent errors that are disrupting production.

    Message example 1:       

    Database Exception in CHtmlPage_Process::OpenPage() with tag OpenEx  

        The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.
    Communication link failure.

    Message example 2:

           Database Exception in ExecuteSQL for 'SET XACT_ABORT OFF'. Error: The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.
    Communication link failure

    We have tried ODBC SQL Server Native Client 11.0 version 2011.110.6518.00 as well.  The same error occurs.

    Both drivers are set to Pool connections with a Timeout of 60.  

    The programmers assure me they are using proper transaction control procedures. 

    In this same environment the JDBC and IIS connections are remaining open in a sleeping status.  Sometimes over 150.

    We are stumped and need some Microsoft technical guidance on settings to change or whether or not there is a known defect with the Drivers, SQL Server 2016, or WS 2016.

    We appreciate your help.


    • Edited by DevraK Friday, February 9, 2018 10:00 PM
    Friday, February 9, 2018 9:58 PM

All replies

  • Is the ODBC client running on the same machine(s) as the stable IIS and JDBC applications? Could it be the root cause is intermittent network connectivity loss specific to those client machines rather than a broader connectivity issue?

    I wouldn't expect transaction control to result in this symptoms but if the application code uses a persistent connection instead of leveraging connection pooling (using the open late, close early pattern), it will be more likely to suffer problems due to connectivity blips. Maybe that's the difference compared to the other apps.

    I haven't heard of intermittent issues with ODBC Driver 11 for SQL Server but you might try the latest version, ODBC Driver 13.1 for SQL Server (https://docs.microsoft.com/en-us/sql/connect/odbc/windows/microsoft-odbc-driver-for-sql-server-on-windows). You could also increase the ConnectionRetryCount from the default of 1.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, February 10, 2018 2:17 AM
    Moderator
  • Dan,

    This problem persists.  Yes, the ODBC client is running on the sane server running IIS and JDBC applications.  This is load balanced environment with two WS 2016 servers.  The sessions are scheduled to persist for up to 8 hours.  But these disconnections are happening all at once on an individual server or sometimes both, most often in the early morning hours but this is not a constant.  

    Tuesday, July 10, 2018 4:37 PM