set nocount on

--insert into msdb.dbo.tempdb_log_usage_v2
select GETDATE() as 'collection_time',p.*, t.*   from
(
select ss.session_id as 'ss_session_id'
    ,ss.login_time
    ,ss.host_name
    ,ss.program_name
    ,ss.client_interface_name
    ,ss.login_name
    ,ss.status as 'session_status',
    ss.last_request_start_time
    ,ss.last_request_end_time
    ,er.start_time
    ,er.status as 'request_status'
    ,er.command,qt.text as 'parent_query'
    ,SUBSTRING (qt.text,er.statement_start_offset/2,(CASE WHEN er.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE er.statement_end_offset END - er.statement_start_offset)/2) as 'individual_query'
    ,er.sql_handle
    ,er.plan_handle
    ,er.database_id as 'er_database_id'
    ,er.open_transaction_count
    ,er.transaction_id as 'er_transaction_id'
    ,er.total_elapsed_time/1000 as'running_time_sec'
    ,er.reads,er.writes,er.logical_reads
from sys.dm_exec_sessions as ss
    left join sys.dm_exec_requests as er
    on ss.session_id=er.session_id
    cross apply sys.dm_exec_sql_text(er.sql_handle)as qt
where ss.status='running') as p

inner join (
select st.session_id
    ,st.transaction_id as 'dbt_transaction_id'
    ,dbt.database_id
    ,dbt.database_transaction_begin_time
    ,dbt.database_transaction_log_record_count
    ,dbt.database_transaction_log_bytes_used
    ,dbt.database_transaction_log_bytes_reserved
    ,dbs.log_reuse_wait_desc
    ,(select cntr_value/1024 as 'log_usage_mb' from sys.dm_os_performance_counters
        where object_name like'%:Databases%'
            and counter_name='Log File(s) Used Size (KB)'
            and instance_name='tempdb') as 'tempdb_log_usage_mb'
from sys.dm_tran_database_transactions as dbt
    inner join sys.dm_tran_session_transactions as st
        on dbt.transaction_id=st.transaction_id
    inner join sys.databases as dbs with (NOLOCK)
        on dbt.database_id=dbs.database_id
where dbt.database_id=2 and dbt.database_transaction_log_bytes_reserved>0 )
as t
on p.ss_session_id=t.session_id
and p.er_transaction_id=t.dbt_transaction_id
where p.ss_session_id<>@@SPID
option (recompile)