DB connection is closed after it is idle for some time

Question

 

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

Answer 1

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

Answer 2

 

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

Answer 3

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?

Answer 4

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

 

Answer 5

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