Monitoring SQL transactions and their elapsed time in seconds

Question

i'm currently challenged with the task to monitor an mssql server and i'd like to get an overview of pending/running transactions in tempDB.

I use following query to get a table of transactions and their elapsed_time_seconds

   SELECT
   a.session_id
 , a.transaction_id
 , a.transaction_sequence_num
 , a.elapsed_time_seconds
 , b.program_name
 , b.open_tran
 , b.STATUS
FROM sys.dm_tran_active_snapshot_database_transactions a
JOIN sys.sysprocesses b ON a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

The problem: This query does not return anything if the table is empty. Not even NULL. Additonally I dont speak SQL.

I've tried to place COALESCE and ISNULL in the query at different rows but this didn't help.

Can i somehow extend the query so that it returns 0 0 0 0 0 in the table row if nothing else is returned?

Thanks and best regards Manuel

Answer 1

It's unclear why you would want this, but you can start off with a dummy VALUES constructor and left-join everything else.

Note that sysprocesses is deprecated

Note also that dm_tran_active_snapshot_database_transactions only shows transactions in the snapshot isolation level. You probably want dm_tran_active_transactions instead.

SELECT
   ISNULL(t.session_id, 0)
 , ISNULL(t.transaction_id, 0)
 , ISNULL(t.transaction_sequence_num, 0)
 , ISNULL(t.elapsed_time_seconds, 0)
 , s.program_name
 , ISNULL(s.open_transaction_count, 0)
 , s.STATUS
FROM (VALUES(0)) v(dummy)
LEFT JOIN
  sys.dm_tran_active_snapshot_database_transactions t
    JOIN sys.dm_exec_sessions s ON s.session_id = t.spid
  ON 1 = 1
ORDER BY elapsed_time_seconds DESC