This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a T-SQL script to find out a list of open transactions in SQL Server. If application developers are not closed database transactions correctly, there are a chance for deadlock/lock in the database.
All application/database developers should make sure that all transactions are closed which are opened earlier.
DBA can use below script to find out a list of Open Transaction in SQL Server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT [tst].[session_id] AS SessionID ,[des].[login_name] AS LoginName ,DB_NAME (tdt.database_id) AS DatabaseName ,[tdt].[database_transaction_begin_time] AS TransactionBeginTime ,[tdt].[database_transaction_log_bytes_used] AS LogBytesUsed ,[mrsh].text AS QueryText ,[ph].[query_plan] AS QueryPlan FROM sys.dm_tran_database_transactions [tdt] JOIN sys.dm_tran_session_transactions [tst] ON [tst].[transaction_id] = [tdt].[transaction_id] JOIN sys.[dm_exec_sessions] [des] ON [des].[session_id] = [tst].[session_id] JOIN sys.dm_exec_connections [dec] ON [dec].[session_id] = [tst].[session_id] LEFT OUTER JOIN sys.dm_exec_requests [der] ON [der].[session_id] = [tst].[session_id] CROSS APPLY sys.dm_exec_sql_text ([dec].[most_recent_sql_handle]) AS [mrsh] OUTER APPLY sys.dm_exec_query_plan ([der].[plan_handle]) AS [ph] ORDER BY [TransactionBeginTime] ASC GO |
Leave a Reply