RSS

How to find an idle session with an open transaction

10 May

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

Io_Performance

 

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

io_performance_2

 

The Result is Session ID No ((56))

 
Leave a comment

Posted by on May 10, 2013 in Performance MSSQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s