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
/************************************/

Detailed Blocking Information With Query Information In Sql Server

  The servers which are extensively suffered from blocking are normal. To find detail info about blocking, You can use below query.

/************************************/
SELECT
owt.session_id AS waiting_session_id,
owt.blocking_session_id,
DB_NAME(tls.resource_database_id) AS database_name,
(SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
(CASE WHEN ers.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE ers.statement_end_offset
END
- ers.statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
CASE WHEN owt.blocking_session_id > 0
THEN (
SELECT
est.[text] FROM sys.sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
WHERE sp.spid = owt.blocking_session_id)
ELSE
NULL
END AS blocking_query_text,
(CASE tls.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
ELSE (SELECT OBJECT_NAME(pat.[object_id], tls.resource_database_id)
FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)
END
) AS object_name,
owt.wait_duration_ms,
owt.waiting_task_address,
owt.wait_type,
tls.resource_associated_entity_id,
tls.resource_description AS local_resource_description,
tls.resource_type,
tls.request_mode,
tls.request_type,
tls.request_session_id,
owt.resource_description AS blocking_resource_description,
qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO
 /************************************/

Script To Collect Performance Metrics From SQL Server Query Analyzer

/*************************************************/ 
USE Master
GO
Create Table Perfmon (Object_name Varchar (200),
Counter_name varchar (300) , Instance_name varchar (100),
cntr_value bigint, cntr_type bigint , date varchar (20))
GO 
Insert into Perfmon
SELECT object_name , counter_name , instance_name , cntr_value, cntr_type
, convert (varchar (20),getdate () , 120) as date
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME in 
(
'MSSQL$Servername:Databases' ,
'MSSQL$Servername:General Statistics',
'MSSQL$Servername:Buffer Manager' ,
'MSSQL$Servername:Locks'
)
AND counter_name in 
( 
'Transactions/sec' ,'User Connections','Page life expectancy',
'Buffer cache hit ratio', 'Buffer cache hit ratio base' ,
'Free pages','Total pages','Target pages','Lock Wait Time (ms)'
)
Select @@servername
GO
Select *, case
when cntr_type  = 65792  Then 'instant_value'
when cntr_type = 272696576 Then 'Incremental'
When cntr_type = 1073939712 Then 'Instant fraction'
When cntr_type = 537003264 Then 'Use this with Base value'
else 'null' end  
from Master.dbo.perfmon
/*************************************************/