SELECT
	CASE transaction_isolation_level 
	WHEN 0 THEN 'Unspecified' 
	WHEN 1 THEN 'ReadUncommitted' 
	WHEN 2 THEN 'ReadCommitted' 
	WHEN 3 THEN 'Repeatable' 
	WHEN 4 THEN 'Serializable' 
	WHEN 5 THEN 'Snapshot' END AS ILevel
,       r.session_id
,       requested_memory_MB = x.requested_memory_kb/1000. 
,       granted_memory_MB = x.granted_memory_kb/1000.
,		x.query_cost
,       r.start_time
,	RunTime = DATEDIFF(s,r.start_time,getdate())/60.
,       TotalElapsedTime_min = r.total_elapsed_time/1000./60.
,       r.[status]
,       r.command
,	r.percent_complete
,       DatabaseName = DB_Name(r.database_id)
,       r.wait_type
,       r.last_wait_type
,       r.wait_resource
,       r.cpu_time
,       r.reads
,       r.writes
,       r.logical_reads
,       t.[text] AS [executing batch]
,       SUBSTRING(
                                t.[text], r.statement_start_offset / 2, 
                                (       CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text]) 
                                                 ELSE r.statement_end_offset 
                                        END - r.statement_start_offset ) / 2 
                         ) AS [executing statement] 
,       p.query_plan
FROM
        sys.dm_exec_requests r
CROSS APPLY
        sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY     
        sys.dm_exec_query_plan(r.plan_handle) AS p
LEFT JOIN
	sys.dm_exec_query_memory_grants         x
ON
	x.session_id  = 	r.session_id 
WHERE 
		r.session_id != @@spid       
ORDER BY 
        r.total_elapsed_time DESC;

	   [individual query] = substring(t.text , r.statement_start_offset / 2 + 1, (
								case
									when r.statement_end_offset = - 1
									then len(convert(nvarchar(max), t.text)) * 2
									else r.statement_end_offset
								end - r.statement_start_offset
								) / 2),

-- This query returns log file space used by all running transactions.
select
    SessionTrans.session_id as [SPID],
       percent_complete,
    total_elapsed_time as [Elapsed Time],
    cpu_time as [CPU Time],
    wait_type as [Wait Type],
    wait_time as [Wait Time],
    wait_resource as [Wait Resource],
    reads as [Reads],
    logical_reads as [Logical Reads],
    writes as [Writes],
    granted_query_memory as [Query Memory],
    SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text,
    database_transaction_log_record_count as [Log Records],
    database_transaction_log_bytes_used / 1024 as [Log KB Used],
    database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
    database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
    database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
    database_transaction_replicate_record_count as [Replication Records],
    enlist_count as [Active Requests],
    ActiveTrans.transaction_id as [ID],
    ActiveTrans.name as [Name],
       ActiveTrans.transaction_begin_time as [Start Time],
    case transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        when 4 then 'Distributed'
        else 'Unknown - ' + convert(varchar(20), transaction_type)
    end as [Transaction Type],
    case transaction_state
        when 0 then 'Uninitialized'
        when 1 then 'Not Yet Started'
        when 2 then 'Active'
        when 3 then 'Ended (Read-Only)'
        when 4 then 'Committing'
        when 5 then 'Prepared'
        when 6 then 'Committed'
        when 7 then 'Rolling Back'
        when 8 then 'Rolled Back'
        else 'Unknown - ' + convert(varchar(20), transaction_state)
    end as 'State',
    case dtc_state
        when 0 then NULL
        when 1 then 'Active'
        when 2 then 'Prepared'
        when 3 then 'Committed'
        when 4 then 'Aborted'
        when 5 then 'Recovered'
        else 'Unknown - ' + convert(varchar(20), dtc_state)
    end as 'Distributed State',
    DB.Name as 'Database',
    database_transaction_begin_time as [DB Begin Time],
    case database_transaction_type
        when 1 then 'Read/Write'
       when 2 then 'Read-Only'
        when 3 then 'System'
        else 'Unknown - ' + convert(varchar(20), database_transaction_type)
    end as 'DB Type',
    case database_transaction_state
        when 1 then 'Uninitialized'
        when 3 then 'No Log Records'
        when 4 then 'Log Records'
        when 5 then 'Prepared'
        when 10 then 'Committed'
        when 11 then 'Rolled Back'
        when 12 then 'Committing'
        else 'Unknown - ' + convert(varchar(20), database_transaction_state)
    end as 'DB State',
    command as [Command Type],
    --open_transaction_count as [Open Transactions],
    open_resultset_count as [Open Result Sets],
    row_count as [Rows Returned],
    nest_level as [Nest Level]
from sys.dm_tran_active_transactions ActiveTrans (nolock)
inner join sys.dm_tran_database_transactions DBTrans (nolock) on DBTrans.transaction_id = ActiveTrans.transaction_id
inner join sys.databases DB (nolock) on DB.database_id = DBTrans.database_id
left join sys.dm_tran_session_transactions SessionTrans (nolock) on SessionTrans.transaction_id = ActiveTrans.transaction_id
left join sys.dm_exec_requests ExecReqs (nolock) on ExecReqs.session_id = SessionTrans.session_id and ExecReqs.transaction_id = SessionTrans.transaction_id
outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes
       --AND DB.Name  = 'xxx'