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

[tribule]

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.

[BKBK]

It's unfortunate that the forum is still not publishing posts to this particular thread. This in spite of the information from the ColdFusion Team that it should work.

[BKBK]

@tribule , yes, of course, you may send me the Java code by private-message.

[BKBK]

[BKBK]

Click on the envelope symbol (top-right) 

[BKBK]

Dear Moderator, my last 2 posts on this thread did not appear in the forum. Could you please look into it?

[Charlie Arehart]

Fwiw, the ever-increasing counr and duration of running cf requests would be consistent with the lockup of the requests on jdbc connection poop check-in/checkout.

So yep, the primary focus will be on what's going on in the db. It may well be about the nature of the queries, or perhaps not. 

We've not heard from tribule if he's found any queries running. That would be a first goal, but he's expressed how once a certain connection level (in the db) is reached, things go awry. We'll be digging into such things more later, as he has been able to arrange access.

But I'll understand if ideas continue to flow here, the "old school" way we've otherwise always had to solve such things. 🙂 

[BKBK]

Yet another test:

1) Remove* from your ColdFusion installation every non-native MS SQL driver class or JAR. By non-native I mean MS SQL driver JAR files or Java classes that you manually imported to ColdFusion after installation.

2) Restart ColdFusion.

3) Reconfigure each MS SQL datasource to use ColdFusion's own MS SQL driver.

*Insurance: please create a backup before deleting 

[tribule]

Will try later after current test, but we've not installed any other drivers/JARs afaik.