Database Simplified Headline Animator

Database Simplified

Saturday, 17 September 2011

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.

image

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 :

image

No comments:

Post a comment