sql 2008查看进程情况和对应语句，检查死锁进程

---------------------------------进程情况1-----------------------
--得到SPID
if object_id('tempdb..#info') is not null drop table #info  
select distinct spid
,hostname
,db_name(dbid) as dbname        
,net_address,loginame,rtrim(program_name) program_name         
,cmd
,case blocked when 0 then '正常' else '被'+rtrim(blocked)+'号进程堵塞' end as blocked
,cast(null as varchar(8000)) as sql 
into #info
from master..sysprocesses  
where hostname<>''

if object_id('tempdb..#link_info') is not null drop table #link_info 
create table #link_info(EventType varchar(100) null,Parameters varchar(10) null,EventInfo varchar(8000) null )
--取执行语句
declare @spid int 
declare link_cursor cursor local for select spid from #info 
open link_cursor 
fetch next from link_cursor into @spid
while @@fetch_status = 0
  begin 
    truncate table #link_info 
    insert into #link_info 
    exec('dbcc inputbuffer('+@spid+')') 
update #info set sql=(select top 1 eventinfo from #link_info) where spid=@spid
    IF @@error <> 0 
    BEGIN 
        close link_cursor 
        deallocate link_cursor
        ROLLBACK 
        RAISERROR 20001 '取执行语句失败'
        RETURN 
    END 
    fetch next from link_cursor into @spid
  END
close link_cursor 
deallocate link_cursor   
--查询结果
select * from #info
-------------------------------进程情况----------------------

也可以用下面语句查看SPID的SQL语句
select p.spid,t.text 
from sys.sysprocesses p CROSS  APPLY sys.dm_exec_sql_text( p.sql_handle ) t where p.spid in (59)