Transaction Filling Log Space in SQL Server

Today while working I encounter one of query's to find log space used  by a transaction

Transaction causing log space filled most in database

/***************************************************************************************************/
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'ORDER BY [Log Bytes Used] DESC;

/***************************************************************************************************/

------> Please modify database name

TOP 5 CPU-CONSUMING STATEMENTS In SQL Server

  To quickly identifying which query is consuming more CPU can be found using below query.
/************************************/
SELECT TOP 5
qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins],
qs.execution_count,
qs.min_worker_time/60000000 as [Min CPU Time in mins],
--qs.total_worker_time/qs.execution_count,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
dbname=db_name(qt.dbid),
object_name(qt.objectid) as [Object name]
FROM
sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time in mins] DESC
/************************************/

How To Find Current Running Transaction In Sql Server

   To find detailed info about queries running on sql server can be extracted using below queries.
 /************************************/
use master
Go 
SELECT
SPID,ER.percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN
sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
/************************************/