I was having issues today with my SQL Server Activity Monitor, it kept timing out every time I tried to see active transactions, and so I devised this wee script which returns the currently active transactions in the instance, as well as the text of the procedure running.

The solution is easy and based on two SQL Server objects:

• sys.sysprocesses: This procedure returns detailed information about all sessions connected to the instances, it provides information such as open transactions, sql_handles (used to identify running SQL procedure) and other pertinant information such as “waittypes” and “waitresources”. This provides the basis of the “Processes” tab in the SQL Server Activity Monitor report.
• sys.dm_exec_sql_text(@sql_handle): This DMV takes in the sql_handle, and returns the text of the registered procedure with this handle.

Combining them both together will give you all active transactions on the database, and the text of each running transaction. Like so:

Notice how we use SQL’s CROSS APPLY in order to apply the function to the sys view.

It is important to note that this query returns only the rows that return a value in the DMV: sys.dm_exec_sql_text. This is the nature of CROSS APPLY.

I’ve filtered by open_tran, this indicates whether a transaction is active or not. If a transaction is running, but in a SUSPENDED state, it will not be included in the result set. You can customize filters in this query or remove them entirely if you want to get an overview of all active transactions and their procedure text.

I hope this saves you some time.

NOTE: to get transaction text by specifying an SIDs, you can also use the DBCC INPUTBUFFER (@SID) Console Command. The only problem is that the DBCC commands cannot be used in a query, so to get the text for multiple SIDs, you might have to create a CURSOR that runs DBCC INPUTBUFFER on all active SIDs.