SQL Server Performance

What settings can affect an idle connection?

Discussion in 'SQL Server 2005 General DBA Questions' started by pcsql, May 1, 2008.

  1. pcsql New Member

    If an application on a windows workstation has connected to SQL Server 2005 or SQL Server 2005 Express using ODBC with tcp/ip and there is no activity for a period of time, will the connection be disconnected automatically or will an error or a message be returned to the workstation due to some SQL Server Settings, ODBC settings, TCP/IP settings, and/or OS settings?
    I think the following settings may be related to my questions:
    SQL Server Settings
    • AUTO_CLOSE of a database
    • remote query timeout
    ODBC Settings
    • connection pooling
    TCP/IP Settings
    • KeepAlive
    OS Settings
    • auto-disconnection of idle connections (I have not found this setting yet but I see some posts about it)
    pcsql, May 1, 2008
    #2

  2. MohammedU New Member

    • AUTO_CLOSE of a database - is for closing the database files when there is no activity - it is not advisable to enable
      • remote query timeout - Remote query means if sql is running linked server query it can timeout after certain time based on setting...
    • ODBC Settings
      connection pooling - application reuses the connection without closing it..
      KeepAlive - I am not sure about it but it is nothing to do with idle connection...
      auto-disconnection of idle connections - I don't know...
      Best way it write a kill command script and run it a job if you want to kill idle connections based on your requirements...
    MohammedU, May 1, 2008
    #3

  3. pcsql New Member

    Hi MohammedU,
    I'm trying to figure out why connection has problem if an application is idle in SQL Server 2005 Express.
    pcsql, May 1, 2008
    #4

  4. pcsql New Member

    Hi MohammedU,
    I forget to mention that auto_close is set to true by default in SQL Server Express.
    pcsql, May 2, 2008
    #5

  5. satya Moderator

    http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546852.aspx on your keep-alive information.
    Coming to auto-disconnect SQL Server has no such need, and because of that applications rarely handle the disconnect well. Unless your application will benefit from this and is specifically written to tolerate the disconnection behavior that Oracle needs, I would avoid the temptation to interfere.
    If you trying to manage connection pooling from the SQL Server end of the connection, this is a very bad idea because it results in poor performance in all cases, and data loss in the worst cases.
    satya, May 2, 2008
    #6

  6. pcsql New Member

    Hi Satya,
    I'm trying to resolve a disconnection issue to SQL Server 2005 Express installed in Windows 2003 server. Workstations are running an application which is located in a shared folder in the Windows 2003 Server and that application connects to the SQL Server 2005 Express using ODBC thru TCP/IP. If any workstation has opened the application and the application has connected to the SQL Server and leaves the connection idle for a period of time, the connection will somehow disconnected.
    Is there a setting in Windows 2003 to disconnect idle connection automatically?
    If the issue is caused by shared folder being disconnected, I assume that I will not able to run the application anymore. What kind of error message should be received by the workstation when one tries to do something on the open application if the shared folder is somehow disconnected?
    pcsql, May 2, 2008
    #7

Share This Page