sql sleeping sessions with old open transactions

Question

sql sleeping sessions with old open transactions

dopydb

SSCertifiable

Points: 6687
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 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: 398690 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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dopydb SSCertifiable Points: 6687 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: 398690 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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 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)

Johan Bijnens SSC Guru Points: 135258 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: 6687 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: 398690 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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning