none
SQL Server Connections getting Closed RRS feed

  • Question

  • Hello All,

    I was facing a connection issue to sql server database.

    The server environment details are as below

    Database : MS Sql server 2005 standard edition SP1 version 9.00.2047
    Operating System: Microsoft Windows server2003

    The Client Application and  Sql Server are both on the same machine.

    The application can initially connect successfully to the database, and carry out activities, but after a certain
    time interval the connection breaks to the sql server and the application crashes.
    We are not able to identify the reason for this connection break down.

    We get the following error on connection break down:

    Sql error :0 Sql State:08S01
    I/o Error: Connect reset by peer: Socket write error.

    This application used java and hibernate to connect to the database.

    I also wrote a sample application using .net 2.0 which would establish a connection to sql server and retrive the server date time.
    This connection is left idle for some time and then the application tries to get the date time using this sql
    connection, i got the following error after leaving the connection idle for some time.

    SqlClient.SqlException: A transport-level errror has occured when sending the request to the server(provider: tcp provider , error: 0- An existing connection was forcibly closed by the remote host.)


    Can you please let me know what could be the issue.

    Thanks,

    Kumar B

    Wednesday, July 14, 2010 10:48 AM

Answers

  • Interesting one this...

    This is probably a Network issue rather than a SQL issue; have your Networks team investigated the issue?

    In SQL Server; check what the remote timeout is set at; you may need to increase the value.  Also check the Database options; is "Auto Close" selected?  If it is then turn this option off.


    Tony C
    Wednesday, July 14, 2010 12:08 PM
  • SQL Server does not generally close connections to the server unless the connection is actually lost.  I would look at your Java program to see if there is a timeout of somekind.

     

    Wednesday, July 14, 2010 1:28 PM
    Moderator
  • In addition, if you are using the SQL Server Native Client, MS added a retry of the connection if the connection to the server is lost for Database Mirroring and Clustering support.  This would cause the connection to auto-retry at least one time, before the application even knows it has been disconnected.

     

    Wednesday, July 14, 2010 1:30 PM
    Moderator

All replies

  • Kumar B

    First of all install Service Pack 3

    http://support.microsoft.com/kb/295029/en-us


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 14, 2010 11:08 AM
    Answerer
  •  

    Hi Kumar,

                     While browsing through , I got the similar problem - solution from the below URL:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/4895d56b-716f-4f82-860f-0aa161d327cc

     Verify last but one reply in the thread, which was proposed as answer. Hope this may help you.

     

    -- Reddy Balaji C.

     

    • Edited by Reddy Balaji C Wednesday, July 14, 2010 11:57 AM removed unwanted lines
    Wednesday, July 14, 2010 11:57 AM
  • Interesting one this...

    This is probably a Network issue rather than a SQL issue; have your Networks team investigated the issue?

    In SQL Server; check what the remote timeout is set at; you may need to increase the value.  Also check the Database options; is "Auto Close" selected?  If it is then turn this option off.


    Tony C
    Wednesday, July 14, 2010 12:08 PM
  • SQL Server does not generally close connections to the server unless the connection is actually lost.  I would look at your Java program to see if there is a timeout of somekind.

     

    Wednesday, July 14, 2010 1:28 PM
    Moderator
  • In addition, if you are using the SQL Server Native Client, MS added a retry of the connection if the connection to the server is lost for Database Mirroring and Clustering support.  This would cause the connection to auto-retry at least one time, before the application even knows it has been disconnected.

     

    Wednesday, July 14, 2010 1:30 PM
    Moderator