#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    114
    Rep Power
    11

    what's the idle timeout of SQL login connection?


    Hi.. I have application which is connected to my SQL 2000 database. Normally our users use Terminal service to the SQL 2000 and application (same box) remotely and connect to the database. But some time the connection break and I have idle login stuck there.

    For example, if I use sp_who, I can see many logins still login to DB even they are out. And sometimes I see duplicate logins too even there is only one person using that login to logon to DB. Is there a way to find out what is the idle timeout of the user login session??

    Is that under SQL server properties -->remote server connections-->Query time-out--> >> sec ?

    Is there a way to limit only 1 session can be allow for each login ??
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    104
    Rep Power
    11
    remote conn timeout has been removed from SQL Server, and I haven't been able to find anything close to it. I think that is what you were looking for.


    you can poll and kill their spid though if you want....
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Using Enterprise Manager select your SQLServer, right click on it and choose Properties-> Connections
    Then you should be able to set the maximum number of concurrent connections for users, see also this for reference:
    User Connections
    This option sets the maximum number of user connections that can exist simultaneously on a server. This value is not the same as the number of users, and it is also not the same as the license limit. If you have an application that requires two connections, they both count toward the limit of this parameter, but only one counts toward the license limit.

    The buffer space needed for user connections is pre-allocated from SQL Serverís memory, so setting this value too large reduces the amount of memory available for data and procedure cache. Making it too small means that your users will be denied a connection.

    This parameter is important. Using Performance Monitor, watch the SQL Server: Max User Connections counter. If it approaches 10 percent of your configured value for the user connections, you may want to increase the value of this parameter. You know you have set this too high, thereby wasting space, if the Max User Connections doesnít even come close to this configuration value. SQL Server needs to be shut down and restarted when this value is changed.
    Aforsythe suggestion is also good, poll the system for idle connections and kill them.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    114
    Rep Power
    11
    >>poll the system for idle connections and kill them

    what is the command to poll idle connection ?

    I used sp_who but almost the all SPID status show as "sleeping" and cmd status "awaiting command" but I am sure that they are still active
  8. #5

IMN logo majestic logo threadwatch logo seochat tools logo