sql sleeping sessions with old open transactions

  • hi all

    we are using a sql management tool (sql sentry) to assist with various investigations etc

    one thing it always reports numerous times a day is "sql sleeping sessions with old open transactions"

    i know the issue will be coming from the application, but when i use the below code to look at the transactions (from sql sentry)

    SELECT TxDesc = '[' + CONVERT(varchar, es.last_request_start_time, 120) + '] (' + CAST(es.session_id AS varchar(6)) + ') ' + host_name + ':' + program_name + ' [' + DB_NAME(dt.database_id) + ']'
    , OpenMinutes = DATEDIFF(minute, es.last_request_start_time, GETDATE())
    FROM sys.dm_exec_sessions es
    JOIN sys.dm_tran_session_transactions st
    ON es.session_id = st.session_id
    JOIN sys.dm_tran_database_transactions dt
    ON dt.transaction_id = st.transaction_id
    WHERE dt.database_id <> 32767
    AND status = 'sleeping'
    AND es.last_request_start_time < DATEADD(MINUTE, -5, GETDATE())
    ORDER BY es.last_request_start_time

    it will sometimes give me a result eg

    TxDesc                                                                                                                                      OpenMinutes

    [2022-04-23 12:23:17] (94) E10APP02:.Net SqlClient Data Provider [ERP]   4423

    [2022-04-23 12:23:17] (94) E10APP02:.Net SqlClient Data Provider [tempdb]         4423

    but the next time i run the code i get zero results, i can keep executing the script and eventually again i will get the results again

    TxDesc OpenMinutes

    [2022-04-23 12:22:36] (135) E10APP01:.Net SqlClient Data Provider [tempdb] 4427

    [2022-04-23 12:22:36] (135) E10APP01:.Net SqlClient Data Provider [ERP] 4427

     

    can anyone advise what is happening here? maybe the application is opening the transactions again when i get zero results?

    any ideas welcome!

    mal

     

    • This topic was modified 3 months, 3 weeks ago by  dopydb.
  • Sounds like the transactions are closing and then opening again.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • hi Grant

    in 4 days we have built up over 500 of these transactions that sleep/open again

    is that normal?

     

    mal

  • Nope. Sounds like the application, or applications, has a problem. Time to dig into the code & configurations there.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • It sounds like a "connection leak" built in.  Either that or connection pooling on steroids that has gotten a bit out of hand and forgetting to deallocate a cursor or commit a transaction in the process.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Have you tried to checkpoint all databases to see if these connections vanish ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • thanks folks for input

    no never checkpointed,

    i guess my main question is, could these sleeping transactions cause me performance issues?

  • Potentially. If they're holding locks, even shared locks, anywhere, you could see contention because of it. However, I suspect you would have seen it by now.

    Main concern would really only be the memory it takes to maintain the open connection.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply