Generally speaking SQL Server does not terminate client connections. Timeout settings for client connections must be configured in the client connecting to the the SQL Server instance.
Answering Your Questions
- Does SQL Server eventually terminate idle connections to the database?
No. SQL Server will not terminate idle connections. Connections are terminated if the client disconnects or actively closes the connection. If the client applications terminates/crashes, then the connection might be closed on the SQL Server side. However, there are cases where a connection can persist. I have personally observed this behaviour, when a Citrix session is terminated due to a long running transaction. The transaction (session) will persist and may have to be manually terminated.
Of course, rebooting the SQL Server or restarting the SQL Server Service will terminate sessions/connections to the instance.
- How to terminate an idle connection to a SQL Server Database within 30 minutes or less? (If I open a query window in SSMS and execute SELECT @@VERSION, it appears the connection continues to exist in a Sleeping State for a really long time.)
You would have to create a job in SQL Server Agent that collects information from the
sys.dm_exec_connections DMVs and act accordingly.
Once you have the desired information ('session_id`) then you could issue the following command:
- How to determine (or test for) the life of an idle connection to a SQL Server Database?
See answer above.
- Is an Application Connection terminated or closed by the Application itself?
Normally, yes. However, if the application crashes or (as in my Citrix experience) a user session terminated, then you might observe sessions/connections that are still visible in the SQL Server instance, because they were not cleaned up. This is an edge case.