DBCC OpenTran - No More For Me

We experienced a transaction log growing out of control. We performed multiple transaction log backup w/out much success. Clearly there was an open transaction.

Normally, I would execute dbcc opentran(). Unfortunately, this didn't return the correct SPID.

I did some digging and came across the following..

I will not be using dbcc opentran() anymore.

SELECT dd.transaction_id,
ds.session_id,
database_transaction_begin_time,
CASE database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END database_transaction_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
END database_transaction_state,
database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved,
database_transaction_begin_lsn,
database_transaction_last_lsn
FROM sys.dm_tran_database_transactions dd
INNER JOIN sys.dm_tran_session_transactions ds
ON ds.transaction_id = dd.transaction_id
WHERE dd.database_id =7 --this is your database ID