sql sleeping sessions with old open transactions

Question

sql sleeping sessions with old open transactions

dopydb

SSCertifiable

Points: 6672
More actions
April 26, 2022 at 1:14 pm

#4022468
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 2 years, 5 months ago by dopydb.

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

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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

dopydb SSCertifiable Points: 6672 More actions · Answer 2

hi Grant

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

is that normal?

mal

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

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

dopydb SSCertifiable Points: 6672 More actions · Answer 6

thanks folks for input

no never checkpointed,

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

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