sys.dm_tran_aborted_transactions (Transact-SQL)
Applies to:
SQL Server 2019 (15.x)
Azure SQL Database
Azure SQL Managed Instance
Returns information about unresolved, aborted transactions on the SQL Server instance.
Table returned
Column name | Data type | Description |
---|---|---|
transaction_id | int | The transaction_id of the aborted transaction. |
database_id | int | The database_id of the aborted transaction. In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a logical server. |
begin_xact_lsn | numeric(25,0) | The starting LSN of the aborted transaction. |
end_xact_lsn | numeric(25,0) | The ending LSN of the aborted transaction. |
begin_time | datetime | The begin time of the aborted transaction. |
nest_aborted | bit | When 1, indicates that the transaction has a nested aborted transaction. |
Permissions
On SQL Server and SQL Managed Instance, requires VIEW SERVER STATE
permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader##
server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE
permission on the database, or membership in the ##MS_ServerStateReader##
server role is required.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
Remarks
The sys.dm_tran_aborted_transactions
DMV shows all aborted transactions on the SQL Server instance. The nest_aborted
column indicates that the transaction has been committed or is active, but there are portions that aborted (savepoints or nested transactions) which can block the PVS cleanup process. For more information, see Troubleshoot accelerated database recovery.
Unresolved, aborted transactions will be removed by the persistent version store (PVS) cleanup process.