SQLServer死锁进程查看引起死锁的电脑IP地址

 

Select 标志,
       进程ID = spid,
       线程ID = kpid,
       块进程ID = blocked,
       数据库ID = a.dbid,
       数据库名 = db_name(a.dbid),
       SQL语句 = te.text,
       用户ID = uid,
       用户名 = loginame,
       累计CPU时间 = cpu,
       登陆时间 = a.login_time,
       打开事务数 = open_tran,
       进程状态 = a.status,
       工作站名 = a.hostname,
       应用程序名 = a.program_name,
       工作站进程ID = hostprocess,
       域名 = a.nt_domain,
       网卡地址 = net_address,
       客户端IP地址 = t.client_net_address,
       服务器IP = t.local_net_address
  From (
         Select 标志 = '死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
                status,hostname,program_name,hostprocess,nt_domain,net_address,s1 = a.spid,s2 = 0,sql_handle
           From master..sysprocesses a join (
                                              Select blocked From master..sysprocesses Group By blocked
                                            ) b On a.spid = b.blocked Where a.blocked = 0
        Union All
         Select '|_牺牲品_>',spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,
                hostname,program_name,hostprocess,nt_domain,net_address,s1 = blocked,s2 = 1,sql_handle
           From master..sysprocesses a Where blocked <> 0

        ) a Left Join sys.dm_exec_sessions s On a.spid = s.session_id
            Left Join sys.dm_exec_connections t On a.spid = t.session_id
            CROSS  APPLY sys.dm_exec_sql_text( a.sql_handle ) te
Order by s1,s2