Since a long time I wanted to leverage the details from the DMV dm_tran_database_transactions into my regular SPID monitoring script. I just tried it today and looks to be useful. Any feedbacks/bugs/thoughts, let me know.
I'm combining sys.dm_os_workers, sys.dm_os_threads, sys.dm_exec_requests optionally sys.dm_exec_query_plan, sys.dm_tran_locks to give a complete picture about a session currently running in SQL Server. To get details about a particular column, refere Books Online.
I'm also attaching .sql file for easier download.
/* ------------------------------------------------------------------------------
SQLSPIDAnalyzer.sql - Script to analyze the status of a particular SPID
Author: SQLSakthi
Input: Session ID or SPID
----------------------------------------------------------------------------------
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND
---------------------------------------------------------------------------------*/
DECLARE @spid int
SELECT @spid = NULL --<<<< Specify the Session ID to be tracked here
IF @spid IS NULL SELECT 'Please specify a Session ID (SPID) to track' AS 'Error'
SELECT f.session_id spid
,[state]
,f.start_time [req_start_time], f.cpu_time, f.logical_reads,f.writes
,f.command cmd, DB_NAME(f.database_id) db
,f.blocking_session_id blkng_spid
,f.wait_type ,f.wait_time ,f.wait_resource ,a.[last_wait_type]
,suspended_ms =
CASE a.wait_started_ms_ticks
WHEN 0 THEN 0
ELSE c.ms_ticks - a.wait_started_ms_ticks
END
,runnable_ms =
CASE a.wait_resumed_ms_ticks
WHEN 0 THEN 0
ELSE c.ms_ticks - a.wait_resumed_ms_ticks
END
--,g.resource_type,g.resource_description,request_mode,request_type,request_status,request_lifetime -- For Lock info
,database_transaction_begin_time xsn_begin
,CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read-only'
Read more: Sakthi's SQL Server Channel