Blog Post

Azure Database Support Blog
1 MIN READ

Lesson Learned #476:Identifying Sleeping Sessions with Open Transactions in Azure SQL Database

Jose_Manuel_Jurado's avatar
Jan 16, 2024

In SQL Server environments, managing session states and transactions is key to ensuring optimal database performance. A particular challenge arises with sessions in a 'sleeping' state holding open transactions for extended periods. These sessions, while seemingly inactive, can hold locks on resources, leading to potential deadlocks or performance degradation.

 

Our focus is on a SQL query designed to pinpoint such sessions. The query utilizes SQL Server's dynamic management views: sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_tran_session_transactions. These views provide real-time data about active sessions, their current requests, and associated transaction details.

 

The heart of the query lies in its ability to filter sessions based on specific criteria: sessions must be in a 'sleeping' state, have an open transaction, and be inactive for over 5 minutes. This precise filtering allows database administrators to quickly identify and address sessions that might contribute to resource locking and overall performance issues..."

 

SELECT 
    ses.session_id,
    ses.login_name,
    req.start_time,
    req.total_elapsed_time,
    req.command,
    req.status,
    trans.transaction_id, 
    ses.status, 
    ses.total_elapsed_time, 
    ses.last_request_start_time, 
    ses.last_request_end_time,
    ses.login_time,
    DATEDIFF(minute, ses.last_request_end_time, GETDATE()) AS InactiveTime
FROM sys.dm_exec_sessions ses
LEFT JOIN sys.dm_exec_requests req ON req.session_id = ses.session_id
LEFT JOIN sys.dm_tran_session_transactions trans ON ses.session_id = trans.session_id
WHERE trans.transaction_id IS NOT NULL
AND DATEDIFF(minute, ses.last_request_end_time, GETDATE()) > 5
and ses.status = 'sleeping'

 

Enjoy!

Published Jan 16, 2024
Version 1.0
  • Hi Jose,

     

    I encountered a problem when I upgraded an application against the backend Azure SQL Database. The upgrade process complained there were active connections in the database so that the upgrade could not continue. But there weren't any running sessions after checking. I only could see a couple of sleeping sessions in the "user" database and a running session in "master" database.  I don't think the running session in "master" database can block the upgrade in the "user" database. Maybe, as you said, a sleeping session can hold open transactions for extended periods. That is why the upgrade process was blocked.

     

    Thank you for your sharing,

     

    Ray