locked
configure a timeout value for an inactive database connection RRS feed

  • Question

  • Hi Gurus,

    1)Can we configure timeout value for an inactive database connection in sql server.

    Example:

    ( If user opened a session and  Session id = 58 , and the session haven't been in use for

    more than 30 Minutes , can we configure TimeOut value to kill the session 58). 

    2)If we can. How can we configure a timeout value for an inactive database connection.

    Thanks in advance.



    Thursday, November 13, 2014 10:57 AM

Answers

  • Do not kill connections which have been open for a long time.  There is no reason to do that.  That is how .NET Connection Pooling works.   Disconnecting them will slow down your application due to errors and reconnections when the connection is required.

    • Marked as answer by Donghui Li Monday, December 8, 2014 8:32 AM
    Thursday, November 13, 2014 12:51 PM
  • There isn't a built in mechanism/option in SQL Server, which you  use to achieve this task. You best to do it in your application code.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Olaf HelperMVP Thursday, November 13, 2014 11:09 AM
    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 11:15 AM
    Thursday, November 13, 2014 11:03 AM

  • ( If user opened a session and  Session id = 58 , and the session haven't been in use for

    more than 30 Minutes , can we configure TimeOut value to kill the session 58). 

    2)If we can. How can we configure a timeout value for an inactive database connection.



    Why are you even worrying about inactive connections it does no harm.  The session is there because SSMS through which you ran the query has finished the query but SSMS window is open the moment you close the window it would be gone. Same is what I have seen with application( although I have limited knowledge about it) you close your application completely session would be gone.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 12:11 PM
    Thursday, November 13, 2014 11:45 AM
  • As such there is no inbuilt function, but you can write you own code and schedule it in a  job which will check and kill the sessions based on your logic.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 12:11 PM
    Thursday, November 13, 2014 12:01 PM
  • I scheduled a job using sysprocesses .


    Better don't do that! Some application keep a connection the whole time open even without activity and they may don't have the capability to reconnect if the Connection was forceable closed by your job; that can cause a lot of trouble.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Bharath_KV Thursday, November 13, 2014 12:44 PM
    • Marked as answer by Olaf HelperMVP Friday, November 14, 2014 7:42 PM
    Thursday, November 13, 2014 12:28 PM
  • Yeah, thanks Sufian,Shanky_621 and Farooq.

    I scheduled a job using sysprocesses .

    • Marked as answer by Donghui Li Tuesday, December 9, 2014 9:52 AM
    Thursday, November 13, 2014 12:05 PM

All replies

  • There isn't a built in mechanism/option in SQL Server, which you  use to achieve this task. You best to do it in your application code.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Olaf HelperMVP Thursday, November 13, 2014 11:09 AM
    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 11:15 AM
    Thursday, November 13, 2014 11:03 AM

  • ( If user opened a session and  Session id = 58 , and the session haven't been in use for

    more than 30 Minutes , can we configure TimeOut value to kill the session 58). 

    2)If we can. How can we configure a timeout value for an inactive database connection.



    Why are you even worrying about inactive connections it does no harm.  The session is there because SSMS through which you ran the query has finished the query but SSMS window is open the moment you close the window it would be gone. Same is what I have seen with application( although I have limited knowledge about it) you close your application completely session would be gone.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 12:11 PM
    Thursday, November 13, 2014 11:45 AM
  • As such there is no inbuilt function, but you can write you own code and schedule it in a  job which will check and kill the sessions based on your logic.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    • Marked as answer by Satish Reddy G Thursday, November 13, 2014 12:11 PM
    Thursday, November 13, 2014 12:01 PM
  • Yeah, thanks Sufian,Shanky_621 and Farooq.

    I scheduled a job using sysprocesses .

    • Marked as answer by Donghui Li Tuesday, December 9, 2014 9:52 AM
    Thursday, November 13, 2014 12:05 PM
  • I scheduled a job using sysprocesses .


    Better don't do that! Some application keep a connection the whole time open even without activity and they may don't have the capability to reconnect if the Connection was forceable closed by your job; that can cause a lot of trouble.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Bharath_KV Thursday, November 13, 2014 12:44 PM
    • Marked as answer by Olaf HelperMVP Friday, November 14, 2014 7:42 PM
    Thursday, November 13, 2014 12:28 PM
  • I concur with Olaf dont do it.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Bharath_KV Thursday, November 13, 2014 12:44 PM
    Thursday, November 13, 2014 12:30 PM
  • Do not kill connections which have been open for a long time.  There is no reason to do that.  That is how .NET Connection Pooling works.   Disconnecting them will slow down your application due to errors and reconnections when the connection is required.

    • Marked as answer by Donghui Li Monday, December 8, 2014 8:32 AM
    Thursday, November 13, 2014 12:51 PM
  • Thanks all...again.

    For your suggestions.

    Friday, November 14, 2014 12:05 PM
  • Do not kill connections which have been open for a long time.  There is no reason to do that.  That is how .NET Connection Pooling works.   Disconnecting them will slow down your application due to errors and reconnections when the connection is required.

    Unless the application has configured the pool to keep the connections open for a terribly long time, this should not be so much of an issue. The default life time is 60 seconds.

    I agree, however, with the others that Satish should not do this. Or at least, he should not do it just to make things look pretty. But there could be situations where this is necessary. For instance, a poorly written .Net application may not dispose or close its connections which can result in an insane number of open connections, and this can have two ramifications:

    1) Wasted memory.
    2) Running out of worker threads.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 16, 2014 10:08 AM
  • Thanks Erland.
    Thursday, December 18, 2014 3:26 AM