SQL SERVER – Finding Open Transactions for Session – @@TRANCOUNT

To inspect Open Transactions for Session nesting on my connection, I use @@TRANCOUNT. It does not directly inspect another session.

-- Run in a fresh session with no existing transaction.
IF @@TRANCOUNT <> 0 THROW 50000, 'Use a fresh session for this example.', 1;
BEGIN TRANSACTION;
BEGIN TRANSACTION;
BEGIN TRANSACTION;
COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS NestingCount, XACT_STATE() AS TransactionState;
ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS AfterCleanup;

The example begins three levels and commits once, returning 2. They belong to one transaction. An inner COMMIT reduces the count. It does not independently harden those changes.

The guard rejects a connection that already has a transaction. The final ROLLBACK cleans up the example and resets nesting. XACT_STATE answers a different question. It identifies absent, committable or uncommittable transaction state.

Inside a procedure, @@TRANCOUNT still describes the caller’s connection. That includes transactions opened earlier. Use permitted session and transaction DMVs for another session and correlate identifiers.

Reference: Transaction nesting count.

Related reading

• Comprehensive Database Performance Health Check
• CONCAT and NULL – SQL in Sixty Seconds #123
• Sleeping vs Suspended Process – SQL in Sixty Seconds #122
• Pinned Tab – SSMS Efficiency Tip – SQL in Sixty Seconds #121
• Split Screen for SSMS Efficiency – SQL in Sixty Seconds #120
• Nonclustered Primary Key – SQL in Sixty Seconds #119
• Recent Execution of Stored Procedure – SQL in Sixty Seconds #118
• Full-Screen Execution Plan – SQL in Sixty Seconds #117
• twitter

Nested transaction count is not independent durability, it is nesting state within the current connection.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.