We are using WAS5.1, MS SQL with the SP3 JDBC drivers (though we were having the same problem with SP1 and SP2).
When testing our web application and running transactions that make calls through the pooled Datasource to SQL that causes an increase in SQL memory usuage. Which to a certain degree in understandable. What happens though is the memory usuage keeps increasing; as if the JDBC driver is not releasing the transaction even though we are closing Result Sets, Statements, clearing warnings, and closing the connection. We are 99.9% certain that on the Java side connections are being cleaned up and closed. Yet the memory keeps disappearing.
Has anyone else experienced this and/or found a solution to it. I feel like I am missing a configuration somewhere.
We've had nothing but problems with MS's own drivers - they are just not very good. Try jTds. Its a little better. However - we have yet to find anything that seems good enough for our production system other than a commercial driver (Data Direct). [ September 20, 2004: Message edited by: Paul Sturrock ]
What the MS Driver does is, that even though you are preparing two statements and using the same connection, it will implicitly open one more connection.And it will go on doing this for every statement.So while you close one connection,I wonder whats happening to the other ones!
You may want to verify this at SQL server end by using exec sp_who
This is one reason why we decided not to use MS driver and went in for DataDirect. There are lots of drivers available for this.jTds is open source.