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)