DBCC OpenTran in Sql Azure

Question

In On Premise SQL Server we have used DBCC OpenTran but we have found that DBCC OpenTran is not available in Azure. What is the alternative for this?

Answer 1

As per Microsoft document , DBCC OpenTran is supported in Azure SQL database. If it does not work, try using sys views like sys.dm_exec_sessions, sys.sysprocesses .

• SELECT * FROM sys.dm_exec_sessions where open_transaction_count=1;

• SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Query:

The following query obtains active transaction information for the current database using dbcc Opentran , sys.sysprocesses view, sys.dm_exec_sessions view.

CREATE TABLE T1(Col1 INT, Col2 CHAR(3));  
GO  
BEGIN TRAN  
INSERT INTO T1 VALUES (101, 'abc'); 
GO  
DBCC OPENTRAN;  
SELECT * FROM sys.sysprocesses WHERE open_tran = 1;
SELECT * FROM sys.dm_exec_sessions where open_transaction_count=1;
ROLLBACK TRAN;  
GO  
DROP TABLE T1;  
GO

Result:

result of DBCC OPENTRAN:

result of sys.sysprocesses view:

result of sys.dm_exec_sessions view:

Refer the Microsoft document sys.dm_exec_sessions T-SQL for additional information.