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)
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...
Hi MohammedU, I'm trying to figure out why connection has problem if an application is idle in SQL Server 2005 Express.
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.
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?