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.
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)