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.

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.

• 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

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

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