SQL Server 查出未提交事务（长事务）SQL

   

dECLARE  @tab TABLE(NAME varchar(100),value varchar(200));
INSERT INTO @tab EXEC('DBCC OPENTRAN WITH TABLERESULTS');
SELECT name,CAST(value AS DATETIME) startDate,getdate() currentDate
,DATEDIFF(s,CAST(value AS DATETIME),getdate()) diffsecond FROM @tab WHERE name in ('OLDACT_STARTTIME')
 SELECT   spid,
         blocked,
         DB_NAME(sp.dbid) AS DBName,
         program_name,         
(select top 1 s.host_process_id
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id where s.host_name=sp.hostname) 应用服务器进程Id,
(select top 1 c.client_net_address
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s  with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id where s.host_name=sp.hostname) 发送SQL的IP,
         waitresource,
         lastwaittype,
         sp.loginame,
         sp.hostname,
         a.[Text] AS [TextData],
         SUBSTRING(A.text, sp.stmt_start / 2, 
         (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end 
         END - sp.stmt_start) / 2) AS [current_cmd]
FROM     sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE  spid =(SELECT CASE WHEN ISNUMERIC(value)=0 THEN -1 ELSE value end 
FROM @tab WHERE name in ('OLDACT_SPID') )