1

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

kriech0r
  • 11
  • 1
  • 1
    If a table has no rows, or you get no rows from a query, then you get no rows; that is by design. Some of those columns aren't defined as an `int` though. `STATUS` from `sysprocesses` is an `nchar(30)` for example. Also, you should fix those aliases; `a` *is* the most common character in `dm_tran_active_snapshot_database_transactions` yes, but I wouldn't use that as the alias. `b` doesn't appear in `sysprocesses` at all though. – Thom A Aug 17 '21 at 13:41
  • Also [`sysprocesses`](https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-ver15) is a compatibility view for SQL Server **2000**. You really shouldn't be using it any more. – Thom A Aug 17 '21 at 13:42
  • Do any of the results from [`[tsql] always return a row`](https://stackoverflow.com/search?q=%5Btsql%5D+always+return+a+row) help? – HABO Aug 17 '21 at 14:02

1 Answers1

0

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43