Home Dashboard Directory Help

dbcc opentran( 0 ) does not reflect open transaction on XTP table by Tony Rogerson SQL



Sign in
to vote
Type: Bug
ID: 811662
Opened: 12/16/2013 3:00:28 AM
Access Restriction: Public
User(s) can reproduce this bug


DBCC OPENTRAN( 0 ) is often used to determine if there are any open transactions and who has the oldest one.

BOL shows it is not deprecated.

Doesn't work unless there has been a none-XTP transaction
Sign in to post a comment.
Posted by Microsoft on 12/30/2013 at 10:48 AM
We have updated the documentation for dbcc opentran.
The update will be published to MSDN soon.

Thanks again for the feedback.
Jos de Bruijn - SQL Server PM
Posted by Microsoft on 12/23/2013 at 8:43 AM
Thanks for submitting this feedback.
DBCC OPENTRAN is used to find transactions holding up log truncation. If active transactions have not generated log they do not hold up truncation, and are not reflected in the results of this DBCC command. This is not specific to XTP.

It looks like the BOL text is not entirely accurate. I will leave this item open to track the update to BOL.

Jos de Bruijn - SQL Server PM
Sign in to post a workaround.
Posted by Microsoft on 12/23/2013 at 8:55 AM
Use dm_tran_active_transactions to find active transactions. Note: this DMV does not return information about XTP-only transactions, i.e., transactions started by the BEGIN ATOMIC of natively compiled stored procedures.

Use the following query to find active transactions that are XTP-only:
select * from sys.dm_db_xtp_transactions
where state=0