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 , 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.