Ever increasing number of database connections with JDBC and ODBC drivers - MSSQL

Forum|Forum|5 years ago
December 23, 2020
2 replies
3052 views

Hi,

We have a legacy CF application that has a number of Java classes as part of its API (we do not have easy access to these). A long-running issue has been that database connections keep rising and are never released. We have tried the JDBC and ODBC drivers for MSSQL with the same overall result. Changing lots of settings in the DSN settings has also yielded no different result. We have a lot of "IF @@TRANCOUNT > 0 COMMIT TRAN" commands when we look in the MSSQL sys.dm_exec_connections table. We wondered if autoCommit was an issue, but have not found a way to effectively use it in the DSN/driver settings. We use the latest MS JSBC driver, version 8.4.1.

We wondered if the Java classes were not properly closing the connections - this seems to be the concensus by many developers, however not even CF can close these "stale" connections and eventually the server crashes (web requests pile up and JDBC connections go to zero).

Any advice appreciated.

This topic has been closed for replies.

BKBK

Community Expert

SQL Server allows a maximum of 32767 connections. Your application might require, say, at most 200 simultaneous database connections. You could therefore, as solution, use the settings of the database to limit the number of connections to 200.

T

tribuleAuthor

Legend

I tried that and at 200 connections, the server fell over. FusionReactor showed JDBC connections fall to zero and web requests skyrocketed - assumed this was because the database could/would not serve pages.

BKBK

Community Expert

I would continue benchmarking. Try 400. If that, too, fails then 800. You're looking for a sweet spot where things work without excessive database connections.

D

Dave Watts

Community Expert

Maybe a dumb question, but have you tried the JDBC drivers that come with CF? (DataDirect Connect for JDBC) There's a reason CF provides these instead of having you download the MS drivers.

Dave Watts, Eidolon LLC

T

tribuleAuthor

Legend

Hi Dave, is the DataDirect driver the same as the MS JDBC driver version 8.4.1? If not, where is it? I checked in the CF Admin and the only thing mentioned for DataDirect is MySQL, which we don't use.

We are using a DSN with the com.microsoft.sqlserver.jdbc.SQLServerDriver class and a JDBC URL like "jdbc:sqlserver://our_listener:1433;databaseName=OURDB;SelectMethod=direct;". I tried the "normal" SQL Server driver as well. Both show rising connections. The non-JDBC MSSQL driver also shows a plateau of heap memory usage, which rises gradually and never goes down. The JDBC driver shows the classic jagged edge heap usage shape (up/down).

D

Dave Watts

Community Expert

No, DataDirect drivers are not the same as MS drivers or any other vendor-provided drivers. DataDirect is a company that makes their own JDBC drivers for a bunch of databases. You should be able to just pick the "MS SQL Server" option in the CF Administrator to use it. If you've selected that, you're NOT using the Microsoft-provided JDBC driver. To use the Microsoft-provided driver, you'd have to choose "Other" and fill out your class and JDBC URL like you appear to have done already.

Dave Watts, Eidolon LLC

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded