locked
JDBC SQL call failure after database service is restarted RRS feed

  • Question

  • Hello ,

     

    I get this error (connection closed) in web applications,  whenever  SQL Server database Service is restarted. When I run the application again, the error disappears.  Granted that there are lots of users and Database gets restarted every night is not helping.

     

    Is there any setting in the JDBC driver properties, where I can tell it to open a closed connection (like a retry count), rather than fail the first time and work the second time. 

     

    We use

    1. SQL SERVER 2005 SP2,

    2. Websphere Protal Server 5.1

    3. SQL SERVER 2005 JDBC driver 1.1(com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource.)

     

    I am wondering if connection pool driver is causing this problem. I do not care about connection pooling, but Websphere doen not seem to like any other JDBC Driver. I don't mind switching to another driver as the last option.

     

    Error is :

    [11/27/07 17:01:59:331 MST] 7202440d WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: 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.getCatalog(Unknown Source)
     at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.getCatalog(WSRdbManagedConnectionImpl.java:676)
     at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.introspectSelf(WSRdbManagedConnectionImpl.java:808)
     at com.ibm.ws.rsadapter.FFDCLogger.introspect(FFDCLogger.java:169)
     at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.introspectSelf(WSJdbcConnection.java:1253)
     at com.ibm.ws.ffdc.IntrospectionLevelMember.getNextMembers(IntrospectionLevelMember.java(Compiled Code))
     at com.ibm.ws.ffdc.IntrospectionLevel.getNextLevel(IntrospectionLevel.java:181)
     at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:67)
     at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:51)
     at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWrite(IncidentStreamImpl.java:396)
     at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWriteLine(IncidentStreamImpl.java:632)
     at com.ibm.ws.ffdc.DiagnosticEngine.dumpObjectAndStack(DiagnosticEngine.java:301)
     at com.ibm.ws.ffdc.DiagnosticEngine.processIncident(DiagnosticEngine.java:147)
     at com.ibm.ws.ffdc.FFDCFilter.filterEngine(FFDCFilter.java:428)

    .......lots of lines

     

    Regards

    Bobba

    Wednesday, November 28, 2007 4:40 AM

Answers

  • Hi Jimmy,

     

    Thanks for the quick reply. It saved me the effort of going into new JDBC driver.  I did not reply sooner as I was working a diffrent option.

     

    I was playing with various connectionpool settings on websphere Appserver. When I made 

    1. "Unused Timeout" 

    2. "Reap Timeout"

    parameters to really low numbers like 1 (basically lower than remote connection timeout on SQL SERVER 2005), the problem seems to disapper. I restared my test SQL Server service numerous times and myweb applications seemed to survive.

     

    It is early for me to celebrate but it seemed to work sofar.

     

    Regards

    Bobba

      

    Thursday, November 29, 2007 10:05 PM

All replies

  • Hi Bobba,

     

    If you are running the application with a live connection to the SQL Server database, when the SQL Server database service is restarted, the exception you received is expected since SQL Server database closes off all connections to restart itself.

     

    If you have a live connection to the SQL Server database, but is in an idle state, when the SQL Server database service is restarted, the connection is again closed, but since your app isn't actively using the connection, you will not see this exception message until the next time you access the connection (at which time, SQL Server database may have already finish restarting).

     

    Retrying/re-running the application will likely have re-established the connection allowing the application to run successfully.

     

    BTW, you mentioned that the SQL Server gets restarted every night.  My recommendation would be to figure out why SQL Server needs to be restarted every night and solve that problem which will help all the different apps that connect to the particular server, instead of trying to solve each of the apps individually.

     

    Currently, the SQL Server 2005 JDBC driver (v1.0, v1.1, v1.2) do not support a mechanism in which the driver itself will automatically retry to connect to the SQL Server when a connection is lost.  Our recommendation for applications that require 24/7 uptime even when SQL Server is down, is to minimize the time in which the application is holding on to an open connection (ie. close all connections that are not needed).  With pooled connection, the connection will go back into the pool awaiting for the next connection open request.  This way it will minimize the chances of having a open connection while SQL Server is restarting.  Combining this with implementing catch block which handles these connection exceptions and retry will help to keep the app running.

     

    HTH,

    Jimmy

     

    Wednesday, November 28, 2007 11:56 PM
  • Hi Jimmy,

     

    Thanks for the quick reply. It saved me the effort of going into new JDBC driver.  I did not reply sooner as I was working a diffrent option.

     

    I was playing with various connectionpool settings on websphere Appserver. When I made 

    1. "Unused Timeout" 

    2. "Reap Timeout"

    parameters to really low numbers like 1 (basically lower than remote connection timeout on SQL SERVER 2005), the problem seems to disapper. I restared my test SQL Server service numerous times and myweb applications seemed to survive.

     

    It is early for me to celebrate but it seemed to work sofar.

     

    Regards

    Bobba

      

    Thursday, November 29, 2007 10:05 PM