Bewerken

sys.dm_tran_aborted_transactions (Transact-SQL)

  • Artikel

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.

See also