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?

As per Microsoft [document](https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-opentran-transact-sql?view=sql-server-ver16), `DBCC OpenTran` is supported in Azure SQL database also. If it doesnot work, try this query `SELECT * FROM sys.sysprocesses WHERE open_tran = 1` — Aswin, Oct 12 '22 at 11:07
Take a look at DMVs in the sys.dm_tran_* space. E.g. `sys.dm_tran_active_transactions`. — Ben Thul, Oct 12 '22 at 14:09
Results will be displayed only if there is an active transaction that exists in the log. Otherwise, it will show message like No active open transactions. — Aswin, Oct 15 '22 at 06:33
You can find the similar question and related answers here: ["How do you find open transactions on Windows Azure SQL Database?"](https://stackoverflow.com/questions/23404529/how-do-you-find-open-transactions-on-windows-azure-sql-database) — Ehsan HP, Oct 16 '22 at 10:54
@Aswin there is open transaction that's why I said its not working — Imran Qadir Baksh - Baloch, Oct 16 '22 at 18:49

score 1 · Accepted Answer · answered Oct 15 '22 at 05:13

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: enter image description here