Share via

Sleeping Sessions with Old Open Transactions issue.

Doria 1,246 Reputation points
2020-09-20T00:32:00.543+00:00

Hello everyone!

How can I resolve this type of issue?

Sleeping Sessions with Old Open Transactions

"Detects when there are sleeping sessions with open transactions older than 10 minutes by default. Such sessions can cause blocking, and can prevent the transaction log from clearing, leading to excessive log file growth and space exhaustion. Additionally, when snapshot isolation is used, they can prevent version cleanup from occurring in tempdb.

The start time, session_id, host, application and database are returned for the oldest 5 transactions by default. The query itself only returns transactions older than 5 minutes by default, to avoid bringing back unnecessary results on systems with many short-running transactions."

25800-sleeping-sessions-with-old-open-transactions.png

Sleeping for 9 days?!!

Thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
0 comments
Accepted answer
  1. Shashank Singh 6,246 Reputation points
    2020-09-20T05:31:31.42+00:00

    The problem is not with SQL Server but either with the code or application not specifically closing the transaction after doing the work. Some application closes transaction when application windows is closed and many time user after doing their work leave that window open and app thinks it is still open. If you read How It Works: What is a Sleeping / Awaiting Command Session you will clearly know that

    When run from the client with a 30 second query timeout the transaction will remain open because the client indicated it wanted to ‘cancel execution' and do no further processing. To get automatic rollback in this situation transaction abort must be enabled. You now have an open transaction with a SPID sleeping/awaiting command.

    The situation can be caused by many other variations but it is always a situation where the SQL Server is waiting for the next command from the client. Outside a physical connection problem these are always application design issues.

    Other such issue can come from if XACT_ABORT is not used in transaction. The blog by Jonathan Kehayias unintended-side-effects-sleeping-sessions-holding-locks talks about the same.

    Moral of the story is you need to look at the procedures and queries running and also discuss this with you application owner who knows the code.

    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Doria 1,246 Reputation points
    2020-09-23T18:30:15.743+00:00

    Thanks for all answers!

    The most difficult thing is to talk to the development team and somehow guide them on how to work with the connections with the database so those sleeping sessions do not occur again... any material on the subject will be welcome!

    Regards.

  2. Doria 1,246 Reputation points
    2020-10-02T14:48:51.313+00:00

    Thanks all!

    I am taking to the development team.

    Regards.