Find an open transaction across any session and any database in SQL Server

Question

I know that we can use select @@trancount and select * from sys.sysprocesses where open_tran = 1 to find if a session has an open transaction in a given database. The problem with these statements is I have to run them in the same window where the transaction was begun.

We have 10 different databases in our dev server. I want to know if there is a way to find out if a transaction is open in any of those databases across any session. I have a problem where I started a transaction to test something and closed the session without committing or rolling it back. I want to find out if the transaction is still open and if so how do I close it?

Answer 1

You can use sp_whoisactive:

In its default mode Who is Active shows any session with an active transaction. This state is reflected in the [open_tran_count] column, the value of which is the depth of nested transactions that have been started—implicitly or explicitly—on the request or session.

Once you have listed the undesired open session, you can kill it using the session ID:

KILL 87;

But you need admin privileges to do so.