DBCC OPENTRAN translated using DMV’s

· Uncategorized

I finally started looking for a decent alternative for the old ‘Sybase’ command to check for open transaction (DBCC OPENTRAN). Here it is:

SELECT st.session_id AS [SPID]
, DB_NAME(dt.database_id) AS [DB name]
, dt.database_transaction_begin_time AS [Started at]
, at.name AS [Transaction type]
, st.transaction_id AS [Transaction id]
FROM sys.dm_tran_database_transactions dt
INNER JOIN sys.dm_tran_session_transactions st on dt.transaction_id = st.transaction_id
INNER JOIN sys.dm_tran_active_transactions at on at.transaction_id = st.transaction_id
WHERE database_transaction_state=4 -- The transaction has generated log records.
AND database_transaction_type = 1 --Read/write transaction
ORDER BY dt.database_transaction_begin_time

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

%d bloggers like this: