DB connection is closed after it is idle for some time

Fri, 07 Dec 2007 15:30:30 Z

I am using MS JDBC driver 2005 1.2 and in-house written connection pooling that was working fine for a number of years. This combination worked OK with SQLserver 2000. But recently we switched to SQLServer 2005 (x64) on WIN Server 2003 Standard x64 eddition. Everything seems work OK during business hours, however, after business hours when there are lesser users and connection stays idle for sometime, I am getting the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.freeConnection(OnlineTransactionManager.java:420)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:707)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:688)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.finalize(OnlineTransactionManager.java:399)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)

Any help would be appreciated

thanks in advance

DB connection is closed after it is idle for some time

Fri, 07 Dec 2007 19:29:56 Z

This is most likely related to a new feature in SQL Server 2005 that enables it to poll, discover, and clean up disconnected sessions. The first step in debugging this would be to disable the "keep alive" feature in SQL Server 2005 to see if it works around your issue. If it does, then it is possible that your network card does not properly support this feature.

http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx

http://support.microsoft.com/kb/137983

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2435917&SiteID=17

Hope that helps,

John

DB connection is closed after it is idle for some time

Fri, 07 Dec 2007 20:40:05 Z

I am not sure how to disable keep alive feature - on a SQL server network configuration TCP/IP property under protocol tab it has Enabled , but when I set it to disbled - I can not connect to a database with the JDBC driver; it also has a number of milliseconds - can I change it to 0 0r -1 ?. Under the IP addresses there is an option to disable, but I think it will disable the TCP/IP connection that I need to have for the driver.

I would appreciate if you give me some steps how to do that.

thanks

DB connection is closed after it is idle for some time

Fri, 07 Dec 2007 23:17:39 Z

Keep alive is not enabled by default. So if you have not enabled it, the problem may not be keep alive. Can you get a JDBC trace log and see if it provides anymore info to troubleshoot?

DB connection is closed after it is idle for some time

Fri, 04 Jan 2008 16:05:03 Z

The default set up had been changed. Specifically, we needed to enable the TCP/IP connectivity to access the DB via JDBC driver.

However, the confusion is what exactly needs to be done to enable/disable “Keep Alive” option as what had been suggested.

The SQL Server configuration manager allows to change the TCP/IP configurations including “KEEP Alive” option for :

1. SQL native client configurations

2. SQL client configuration

3. SQL server Network configuration

Which one of those needs to be changed to enable access via JDBC driver?

Each of the above configurations has two tabs: Protocol and IP addresses. Right now parameter “Enabled” for both tabs have value “Yes” for all the above configurations. As I indicated before, when I put value “No” for parameter Enabled under the Protocol tab, I am not able to connect to the DB.

I would appreciate if you would point out what parameter under what configuration is responsible for enabling/disabling the KEEP ALIVE option.

thanks

DB connection is closed after it is idle for some time

Mon, 29 Sep 2008 13:24:55 Z

Hi Allawin,

I'm also having similar problem after certain idle time.

com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source)
    at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:328)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:312)

Did you find any solutions?

Thanks,
semaj