All transactions/processes information query In MS SQL

set transaction isolation level read uncommitted

SELECT

              [database].*,

              [sp_who2].spid,

              transaction_id ,

          database_Transaction_Begin_Time,

          database_transaction_log_record_count,

              (select top 1 text from sys.fn_get_sql(sql_handle)) as sql_handle_firstquerytext,

         CASE database_transaction_state

         WHEN 1 THEN 'The transaction has not been initialized.'

         WHEN 3 THEN 'The transaction has been initialized but has not generated any log recorst.'

         WHEN 4 THEN 'The transaction has generated log records.'

         WHEN 5 THEN 'The transaction has been prepared.'

         WHEN 10 THEN 'The transaction has been committed.'

         WHEN 11 THEN 'The transaction has been rolled back.'

         WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'

      END database_transaction_state, 

         [sp_who2].status,

         Login,

         hostname,

         Command,

         CPUTime,

         DiskIO,

         ProgramName,

         LastBatch

FROM   sys.dm_tran_database_transactions as a

outer apply (SELECT

 

    database_id,

 

    name as database_name,

 

    [log_reuse_wait_desc]

 

FROM sys.databases

 

where database_id = a.database_id

)

 as [database]

outer apply (select session_id from sys.dm_tran_session_transactions where transaction_id= a.transaction_id) as session_transaction

outer apply (

SELECT  spid,

              sp.sql_handle,

        sp.[status],

        loginame [Login],

        hostname,

        blocked BlkBy,

        sd.name DBName,

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName

FROM master.dbo.sysprocesses sp

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

where sp.spid = session_id

) as [sp_who2]

where database_name='TARGETDB'

order by database_transaction_begin_time asc