question

Doria avatar image
0 Votes"
Doria asked ErlandSommarskog commented

Sleeping Sessions with Old Open Transactions issue.

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered

Is the start_time recent or days old as well? Is the open_transaction_count greater than 1?

This may be a symptom of a connection with an uncommitted transaction returned to the connection pool following an error or coding mistake. Some JDBC connection pooling middleware execute a connection test query periodically on unused pooled connections to make sure the connection is healthy. For example, the Hikari default "connection-test-query" is "SELECT 1". This configuration value can be changed to "IF @@TRANCOUNT > 0 ROLLBACK;" to ensure transactions on unused pooled connections are rolled back.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Doria,

Here is the script to kill all inactive sessions. People those who usually ask for the script to kill sleeping sessions from sp_who2 can also use this script.

 DECLARE @user_spid INT
 DECLARE CurSPID CURSOR FAST_FORWARD
 FOR
 SELECT SPID
 FROM master.dbo.sysprocesses (NOLOCK)
 WHERE spid>50 -- avoid system threads
 AND status='sleeping' -- only sleeping threads
 AND DATEDIFF(HOUR,last_batch,GETDATE())>=24 -- thread sleeping for 24 hours
 AND spid<>@@spid -- ignore current spid
 OPEN CurSPID
 FETCH NEXT FROM CurSPID INTO @user_spid
 WHILE (@@FETCH_STATUS=0)
 BEGIN
 PRINT 'Killing '+CONVERT(VARCHAR,@user_spid)
 EXEC('KILL '+@user_spid)
 FETCH NEXT FROM CurSPID INTO @user_spid
 END
 CLOSE CurSPID
 DEALLOCATE CurSPID
 GO

Best regards,
Cathy


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Doria avatar image
0 Votes"
Doria answered Shashank-Singh commented

Thanks for all the answers guys!

Well, knowing that it is an application issue, is it safe to kill all these sleeping sessions or is there any danger of losing a transaction? In other words, if I kill a specific session, will it roll back the whole transaction?


Thanks again.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Killing is not a solution, it can be counterproductive be warned about it.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

If you kill a session, yes, all work that session has performed will be rolled back. Whether that is safe or not - we can't tell, because we don't know the application. You need to talk to the application team.

However, if they actually have performed updates, they are holding locks. And if they are holding locks, I would expect them to cause blocking and nasty things. So do they really hold any locks? If they do not, it should be safe to kill them. (Although, the application may react badly when it gets an error that the connection has been closed forcibly.)

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No Erland, killing a session would not rollback all the activities it has done, this depends on what actually session was doing. if there were multiple commands working in auto commit mode then your statement is false.

0 Votes 0 ·

To state it another way, killing a session with an uncommitted transaction will rollback the current open transaction, Previously committed transactions, both autocommit or explict, will not be affected.

0 Votes 0 ·

I tacitly meant the work the session has performed inside that open transaction, but that was quite sloppy of them. Thanks for making the clarification!

0 Votes 0 ·
Doria avatar image
0 Votes"
Doria answered DanGuzman commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

A conversation with app devs can be a challenge if they are not proficient in transaction and connection management and you're not well versed in the technology stack they're using. If this were ADO.NET, I'd suggest just wrapping the connection/transaction in a using block to ensure it's automatically closed and disposed when it goes out of scope. But in the case of Java (as evidenced by the jTDS program name), there are too many variations of frameworks and middleware to provide specific guidance. I suggest you collaborate with them in a test environment to reproduce the problem and capture actual SQL activity (batch, RPC, and attention events) with a trace, Keep in mind that the behavior may be controlled indirectly via configuration rather than in code and they may not fully understand the implications of certain settings (e.g. a default IMPLICT_TRANSACTIONS ON).

0 Votes 0 ·
Doria avatar image
0 Votes"
Doria answered ErlandSommarskog commented

Thanks all!

I am taking to the development team.


Regards.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Doria,

Did you find the solution for this. Even, I'm also facing the same issue at my place.
We have so many open sessions which are in sleeping status. when I've monitored those queries are executed in no time and turning into Sleeping session.

Please let me know if you found any solution. It will be much appreciated.

Thank you,,
My Mail i'd: ashokreddysamala@gmail.com.

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog AshokReddySamala-8146 ·

First of all, there were a couple of suggestions in this thread. Did you consider these?

More generally, rather than piggybacking on old threads, it is better to start a new thread, describing your problem from start to end.

1 Vote 1 ·