April 26, 2022 at 1:14 pm
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
April 27, 2022 at 1:07 pm
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
April 27, 2022 at 1:46 pm
hi Grant
in 4 days we have built up over 500 of these transactions that sleep/open again
is that normal?
mal
April 27, 2022 at 2:04 pm
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
April 27, 2022 at 5:06 pm
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
April 27, 2022 at 5:33 pm
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
April 27, 2022 at 6:10 pm
thanks folks for input
no never checkpointed,
i guess my main question is, could these sleeping transactions cause me performance issues?
April 27, 2022 at 7:09 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply