How do you find open transactions on Windows Azure SQL Database?

Question

In SQL Server 2008 R2 you can use the following to find all open transactions when debugging deadlocks:

DBCC OPENTRAN 

However that functionality is not available in Windows Azure SQL Database; it instead throws the errors:

"DBCC command 'OPENTRAN' is not supported in this version of SQL Server."

The sp_who2 stored procedure doesn't exist either.

What is the correct query for getting all open transactions within the Azure version of SQL Server?

Answer 1

Try using the DMVs instead:

SELECT *
FROM sys.dm_tran_active_transactions

SELECT *
FROM sys.dm_tran_database_transactions

SELECT *
FROM sys.dm_tran_session_transactions