0

We are only trying to answer the below questions regarding database connections for an audit. We are not having any issues, and nothing is broken. We are running SQL Server 2019 on Windows. 

  1. Does SQL Server eventually terminate idle connections to the database?

  2. 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.)

  3. How to determine (or test for) the life of an idle connection to a SQL Server Database?

  4. Is an Application Connection terminated or closed by the Application itself?

Thanks in advance. 

Improve this question
2
  • 1
    "We are only trying to answer the below questions regarding database connections for an audit." - Something tells me this isn't the full story. If the auditors are asking these questions, you need new auditors, because they should know the answers to these questions and also know that some of these questions make no sense.
    – J.D.
    Feb 3 at 6:23
  • 1
    "How to terminate an idle connection to a SQL Server Database within 30 minutes or less?" --Please do not inforporate any such thing. This is counter productive. While idle sessions are mostly innocuous if your app is not closing the request there could be many idle connections and may cause worker thread starvation. But this is rare scenario
    – Shanky
    Feb 3 at 11:05

1 Answer 1

Reset to default
2

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

  1. 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.

  1. 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_sessions and sys.dm_exec_connections DMVs and act accordingly.

  • Interesting columns in the sys.dm_exec_connecctions DMV would possibly be:

    • connect_time
    • last_read
    • last_write

  • Interesting columns in the sys.dm_exec_sessions DMV would possibly include:

    • login_time
    • host_name
    • status
    • last_request_start_time
    • last_request_end_time
    • open_transaction_count

Once you have the desired information ('session_id`) then you could issue the following command:

KILL <session_id>;
  1. How to determine (or test for) the life of an idle connection to a SQL Server Database?

See answer above.

  1. 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.