How To Check Open Transactions In SQL Server DBA Scripts

Open transaction not only create performance issue it can wide range of issues. Open transactions are generally not a desirable thing because they might block or cause excessive TempDB growth.

I have encountered once where TempDB was using up all of the available disc space. They were simply executing sp who2 and scrolling through hundreds of records to see if any transactions were active, which I observed while working with them.

Ensure that all transactions that were opened previously are closed by all application/database developers.

An open transaction list in SQL Server can be generated by the DBA using the script below:

 

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

 

 Results:

Method : 2

Helps find active transactions that can be blocking log truncation. If it even, the latest distributed and non-distributed replicated transactions are displayed as well by DBCC OPENTRAN in addition to the oldest current transaction in the database's transaction log. Only active transactions that are present in the log or information about replication in the database are displayed as results. If no transactions are currently active in the log, an informational message is shown.

By using the following command when you have any open sessions in SQL Server Management Studio (SSMS), you may quickly find whether any transactions are open.