Sleeping Sessions with Old Open Transactions issue.

Question

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."

Sleeping for 9 days?!!

Thanks.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.)