How to find an idle session with an open transaction

This Script is helpful to Developers and DBA

Let’s’s begin with my scenario

1- Create Database IO_Performance

CREATE DATABASE IO_Performance
GO

2- Create Table

USE IO_Performance

CREATE TABLE [dbo].[tblCountry](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Code] [char](3) NOT NULL,
[Description] [varchar](50) NOT NULL)

3- Insert Data

USE IO_Performance

BEGIN TRANSACTION

INSERT INTO [dbo].[tblCountry] ([Code], [Description])
VALUES(‘ENG’, ‘ENGLAND’)

Note : My Session ID = 56

 

Now open new session then run this query to now what is the idle session with an open TRAN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL

 

The Result is Session ID No ((56))