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.
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
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'
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