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

How do I find open transactions for any session? One of my clients recently asked me during the Comprehensive Database Performance Health Check. Let me blog about it today.

SQL SERVER - Finding Open Transactions for Session - @@TRANCOUNT opentransaction-800x336

If you have any session open in your SSMS (SQL Server Management Studio), you can easily figure out if there are any open transactions by running the following command.

SELECT @@TRANCOUNT

This is a global variable and will list all the open transaction in the session. You can also use the same inside the Stored Procedure and it will tell you how many transactions are open for the stored procedure when you execute the statement.

For example, you can run the following code.

BEGIN TRAN
	BEGIN TRAN
		BEGIN TRAN
		COMMIT
SELECT @@TRANCOUNT

The code above will give an answer as 2 as there is a total of 3 open transactions and 1 closed transaction leaving a total of open transactions as 2.

Well, that’s it for today. Here are few recent blog posts where I have posted SQL in the Sixty Seconds videos.

You can reach out to me on twitter for further conversation.

Reference: Pinal Dave (https://blog.sqlauthority.com

Author: admin

Leave a Reply

Your email address will not be published.