SELECT
R.session_id AS spid
, DatabaseName = DB_NAME(R.database_id)
, R.blocking_session_id AS blockby
, R.percent_complete
, R.Status
, R.Command
, T.text AS QueryText
, SUBSTRING(
T.text
, (R.statement_start_offset/2) + 1
, (
(
CASE
WHEN R.statement_end_offset < 1 THEN DATALENGTH(T.text)
ELSE (R.statement_end_offset - R.statement_start_offset) / 2
END
)
) + 1
) AS StatementText
, tat.transaction_id AS TranID
, tat.name AS TranName
, tat.transaction_begin_time
, CONVERT(VARCHAR, ( r.total_elapsed_time / 1000 / 3600 )) + 'h ' +
CONVERT(VARCHAR, ( ( r.total_elapsed_time / 1000 ) % 3600 ) / 60) + 'm ' +
CONVERT(VARCHAR, ( r.total_elapsed_time / 1000 ) % 60) + 's' AS elapsed_time
--, R.total_elapsed_time / 1000 AS total_elapsed_time_sec
, R.last_wait_type
, R.wait_resource
--, r.statement_start_offset
--, r.statement_end_offset
, sp.hostname
, sp.program_name
, sp.nt_domain
, sp.nt_username
FROM
sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
LEFT JOIN sys.dm_tran_session_transactions tst
JOIN sys.dm_tran_active_transactions tat
ON tst.transaction_id = tat.transaction_id
ON R.session_id = tst.session_id
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
JOIN sys.sysprocesses sp
ON R.session_id = sp.spid