Find Active Or Long Running Transaction Using DMV

Applies To SQL Server 2005+

DMV : Dynamic Management Views, Provide information about current state of Sql Server. You can use DMV to diagnose or troubleshoot various activity. DMV’s are majorly used to determine performance bottlenecks.

You Can Find All The DMVs Using Following Query

SELECT name, type, type_desc 
FROM sys.system_objects 
WHERE name LIKE 'dm[_]%' ORDER BY name

Description of each DMV can be found at msdn site.

Here We Will See DMVs which can be help us in finding out active transactions.

• sys.dm_tran_session_transactions
• Provides correlation information for associated transactions and sessions.
• sys.dm_exec_sessions
• Provides information about all active user connections and internal tasks
• sys.dm_tran_active_transactions
• Provides transactions for the instance of SQL Server.

Create Table And Populate Data Using Following Script For Performing Tests

Create Database Test_Transaction
Go
Use Test_Transaction
Create Table Test1(Id int Identity(1,1),UserData Varchar(30))
Go
Insert Into User1 Default Values
Go 50

Run Following Command In Query Window

Begin Transaction
Update Test1 Set UserData='Classic' Where Id=1

Note: That Transaction Is Not Completed As No Commit Or Rollback Transaction Has Been Issued.

Now Take New Query Window And Run Following Query To See Is There Any Active Transaction

Select B.session_id,A.transaction_id,C.transaction_begin_time,
DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds,B.HOST_NAME,B.program_name,B.login_name,
b.login_time as UserLoginTime 
from sys.dm_tran_session_transactions A
Join sys.dm_exec_sessions B On A.session_id=B.session_id 
Join sys.dm_tran_active_transactions C On A.transaction_id =C.transaction_id

Output :

session_id	transaction_id	transaction_begin_time	TimeTaken_In_Seconds	HOST_NAME	program_name	login_name	UserLoginTime
53	1944398	2011-09-17 11:27:42.777	58	STIPLGGNL008	Microsoft SQL Server Management Studio - Query	sa	2011-09-17 11:26:00.783

Please See Following Screen as Above table Is Difficult to Fit in.

The Most Important Is Time Taken By Query Which can be Found In TimeTaken_In_Seconds. If You Wish To See Time In Minutes Then You Can Modify Above Query and Write Minutes In-Place of Second

How To Know Which Query is Running From Above Result ???

You Can Take Session_Id From Above Result And Run Following Query

DBCC InputBuffer(53)

output :