Open_transaction_count in sys.dm_exec_sessions

This news may be a few months old, but it is worth noting that there is now a column called open_transaction_count in the sys.dm_exec_sessions in SQL Server 2012.

This should put away any need for you to use sys.sysprocesses for session information.



Beware, it can cause an ambiguity in existing queries, as this column already exists in sys.dm_exec_requests for the number of open transactions in a request. It is pretty handy to join these DMV's together on session_id.

Here's an example of a query I use regularly (a shortened form of a previous post) which needed me to add the source alias for sys.dm_exec_sessions:

select                     

  r.session_id

, s.host_name

, s.program_name

, r.status

, r.blocking_session_id

, DBName = db_name(r.database_id)

, r.command

, r.wait_type

, r.wait_time

, s.open_transaction_count

, r.cpu_time

, r.total_elapsed_time

, r.reads

, r.writes

, r.logical_reads

, est.[text]

, offsettext    =      SUBSTRING (est.[text], r.statement_start_offset/2 + 1,

                                    CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))

                                         ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1

                                    END)

, r.statement_start_offset

, r.statement_end_offset

from sys.dm_exec_requests r

inner join sys.dm_exec_sessions s on r.session_id = s.session_id

cross apply sys.dm_exec_sql_text (r.sql_handle) est

where s.session_id > 5

and s.session_id <> @@SPID