How to capture the Rollback transactions in Azure managed instance , right now we are using general purpose tier

Question

we are trying to update the table data , unfortunately we are getting rollback few rows of data only , example we updated 200 rows at a time , In the 200 rows 120 rows updated successfully left of the rows are rollback after few hours this was happened in Azure managed instance , database side and API side also same thing happened , we tried manually update the data through SSMS, Update command showing successfully but same thing happened kindly share the relevant information how do I retrieve the rollback transactions data in Azure managed instance.

Answer 1

Hi @Srinivas Naraipeddi,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand, certain rows are rolling back during your update operations in Azure SQL Managed Instance.

You can try following steps to retrieve information about rolled-back transactions.

1. Enable SQL Server Audit to Capture Transaction Rollbacks: SQL Server Audit can help you capture specific database activity, including transaction rollbacks. You can set it up using below mentioned query.

CREATE SERVER AUDIT [Audit_Rollback_Transactions]

TO FILE (FILEPATH = 'C:\AuditFiles')

WITH (ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT [Audit_Rollback_Transactions] WITH (STATE = ON);

2.Create a Database Audit Specification: Set up a specification to monitor ROLLBACK TRANSACTION events. This will log details about transactions, including rollbacks, to your specified audit file. You can retrieve these logs for investigation. You can set it up using below mentioned query.

CREATE DATABASE AUDIT SPECIFICATION [RollbackAudit]

FOR SERVER AUDIT [Audit_Rollback_Transactions]

ADD (TRANSACTION_GROUP);

ALTER DATABASE AUDIT SPECIFICATION [RollbackAudit] WITH (STATE = ON);

 3. Query Dynamic Management Views (DMVs): Azure SQL Managed Instance provides DMVs that can help you trace transaction history and identify rollback causes.

• sys.dm_tran_active_transactions: Lists active transactions, including their state.
• sys.dm_tran_session_transactions: Tracks transactions per session, which may provide insight if transactions are incomplete.
• sys.dm_tran_database_transactions: Shows information about database transactions, including commit and rollback details.
• sys.dm_exec_requests: Shows current requests, where status  might indicate whether a rollback is occurring.

SELECT

    at.transaction_id,

    at.name,

    at.transaction_begin_time,

    at.transaction_state,

    dt.database_transaction_log_record_count,

    dt.database_transaction_log_bytes_reserved

FROM sys.dm_tran_active_transactions at

JOIN sys.dm_tran_database_transactions dt

ON at.transaction_id = dt.transaction_id

WHERE at.transaction_state IN (4, 5); -- state 4 is Rollback, 5 is Terminated

 4. Use the Transaction Log to Retrieve Rollback Data: In Managed Instances, you can use fn_dblog to retrieve information from the transaction log. The below mentioned query will give you entries for rolled-back transactions, which you can use to identify any aborted operations affecting your updates.

SELECT

    [Current LSN],

    [Operation],

    [Transaction ID],

    [Transaction Name],

    [Begin Time],

    [Transaction SID],

    [SPID]

FROM sys.fn_dblog(NULL, NULL)

WHERE [Operation] = 'LOP_ABORT_XACT';

5.Enable Extended Events for Detailed Tracking: You can set up an Extended Event session to capture rollback events and transaction details in real time using below mentioned query.

CREATE EVENT SESSION [TransactionRollback] ON SERVER

ADD EVENT sqlserver.rollback_transaction

ADD TARGET package0.event_file

(SET filename = 'C:\RollbackEvents.xel', max_file_size=(5), max_rollover_files=(5));

ALTER EVENT SESSION [TransactionRollback] ON SERVER STATE = START;

6. Check for Deadlocks or Long-Running Queries: Rollbacks might occur due to deadlocks or long-running transactions. Monitor for deadlocks using the sys.dm_exec_requests DMV.

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

We request you to refer the below mentioned links for more information.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-active-transactions-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-session-transactions-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/auditing-configure?view=azuresql

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.