locked
Active Connection session time out issue RRS feed

  • Question

  • Hello 

    I use select * from sys.dm_exec_sessions where program_name = 'MyNorthwind' query to see how many instance get connected to database by using my application.

    In my application I set Application Name attribute "MyNorthwind" in connection string.

    But here Issue is If user is not interacting in application for some time then may b session expires & it will remove entry from the result from above query.

    I want to do something like Untill connecis not closed or not removed from Connection pool it must be monitored by database or server.

    So for that purpose I found this way.

    But If user not using application for some time just keep it connected n open still it remove entry from above query result .

    So is there any way to achieve my goal ?

    Thanking you in advance..

    Regards

    Vipul

    • Changed type Naomi N Friday, December 20, 2013 4:38 PM question
    Friday, December 20, 2013 6:17 AM

Answers

  • >>>>Like I only want to allow 3 users that can login in sqlserver by using my applicaiton.

    Specify in connection string

     max pool size=3"

    Or take a look into DDL triggers to filter on program name.

    create login AuditLogin with password = ‘AuditLoginPswd’
    go
    /*Create a very simple login trigger */
    create trigger AuditLogin_Demo
    /* server means instance level*/
    on all server
    with execute as self
    /* We specify the logon event at this stage
    – If there are more than one connections,
    – Issue a rollback*/
    for logon
    as begin
    IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
    original_login_name = ‘AuditLogin’) > 3
    ROLLBACK;

    end
    go

    I would prefer the first method .....



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP, Editor Friday, December 20, 2013 7:20 AM
    • Proposed as answer by Fanny Liu Thursday, December 26, 2013 10:37 AM
    • Marked as answer by Fanny Liu Wednesday, January 1, 2014 9:19 AM
    Friday, December 20, 2013 7:20 AM
    Answerer

All replies

  • It is unclear what you are trying to achieve. I think it is how the application by using connection string property (connection pool)  manages connections to SQL Server ..How the application managed connections  what method, Dispose, Close????

    http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/10/08/connection-pooling-for-the-sql-server-dba.aspx

    Take a look also into

    SELECT *
    FROM sys.dm_exec_connections


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, December 20, 2013 6:29 AM
    Answerer
  • Hello

    My task is :

    I want to limit number of users that are connected through application.

    Like I only want to allow 3 users that can login in sqlserver by using my applicaiton.

    For that I want to use above mentioned query.

    I gives me proper result untill application idle. If application is idle then it will remove it from pool.

    So at that time I am not getting proper result.

    I also read some where Connection pool maintain that data If we again query by using same application then it will fetch connection from pool. It will mark that connection as Invelid when we close connection.

    So If my applicaiton still open then I think it must not mark as invelid in pool. So Is there any way to get these information?

    Or if you have any other way to achieve my task then please help me.

    I only want to limit users to connect server by using my applicaiton.

    Or is there any way that we can make live my application session?

    Thanking you in advance..

    Regards

    Vipul


    Friday, December 20, 2013 6:45 AM
  • >>>>Like I only want to allow 3 users that can login in sqlserver by using my applicaiton.

    Specify in connection string

     max pool size=3"

    Or take a look into DDL triggers to filter on program name.

    create login AuditLogin with password = ‘AuditLoginPswd’
    go
    /*Create a very simple login trigger */
    create trigger AuditLogin_Demo
    /* server means instance level*/
    on all server
    with execute as self
    /* We specify the logon event at this stage
    – If there are more than one connections,
    – Issue a rollback*/
    for logon
    as begin
    IF ORIGINAL_LOGIN()= ‘AuditLogin’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
    WHERE is_user_process = 1 AND
    original_login_name = ‘AuditLogin’) > 3
    ROLLBACK;

    end
    go

    I would prefer the first method .....



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP, Editor Friday, December 20, 2013 7:20 AM
    • Proposed as answer by Fanny Liu Thursday, December 26, 2013 10:37 AM
    • Marked as answer by Fanny Liu Wednesday, January 1, 2014 9:19 AM
    Friday, December 20, 2013 7:20 AM
    Answerer