SQL Server 2012 – Find Open Transactions Across Any Session and Database

sql serversql-server-2012transaction

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?

Best Answer

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.

Related Solutions

Sql-server – Linked Servers pointing to same server (localhost) causing “Transaction context in use by another session” error

Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another.

http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx

SQL Server – How to Find T-SQL of a Sleeping Session Blocking Another Process

How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'?

New version this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session.

You can use

SELECT db_name(S.database_id) AS DatabaseName,
        ST.text
FROM   sys.dm_exec_connections AS C
       JOIN sys.dm_exec_sessions AS S ON S.session_id = C.session_id
       OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
WHERE  C.session_id = 75;

Best Answer

Related Solutions

Sql-server – Linked Servers pointing to same server (localhost) causing “Transaction context in use by another session” error

SQL Server – How to Find T-SQL of a Sleeping Session Blocking Another Process

Related Question