All 7 Transactions & ACID questions
More in Transactions & ACID
What is a transaction in SQL Server, and why is grouping multiple statements into one important? What does ACID stand for, and what does each letter represent in the context of database transactions? How do you use a SAVE TRANSACTION savepoint within a larger transaction to allow partial rollback of just a portion of the work? What is the difference between an implicit and an explicit transaction in SQL Server? How would you design a distributed transaction that needs to span operations across two different database servers, and what additional complexity does this introduce? How do long running transactions negatively impact overall system performance, and what strategies help avoid this problem?
Advertisement
Back to results for “Transactions & ACID”
SQL Server › Transactions & ACID

How do you check whether a transaction is currently active and view its properties within a session?

Intermediate

Answer

You can use the @@TRANCOUNT system function to check how many transactions are currently active and nested within the current session, and the XACT_STATE function to determine whether the current transaction, if any, is in a normal, committable state or has become an uncommittable state due to an earlier error.
BEGIN TRANSACTION;
PRINT @@TRANCOUNT; -- shows 1
SELECT XACT_STATE(); -- shows 1 for a normal, committable transaction
Real-world example A developer troubleshooting an unexpected error checks @@TRANCOUNT within their stored procedure to confirm exactly how many nested transactions are currently open, helping them understand and fix a mismatched BEGIN and COMMIT pairing.

Common follow-ups: What does an XACT_STATE value of negative one actually indicate?;How do nested transactions in SQL Server actually behave regarding commit and rollback?

Error Handling with TRY CATCH;Isolation & Locking
All 7 Transactions & ACID questions