Sql Server 查询死锁进程及抛弃进程 ..txt

--Sql Server2005 查询死锁进程和抛弃进程 .
 --trasen_db为数据库名称 

--引起死锁(阻塞)的进程(阻塞源头) 
--kill源头的进程ID,一般可解决阻塞 
select   distinct 
'进程ID '                             =   str(   spid,   4   ), 
'进程ID状态 '                     =   convert(   char(10),   status   ), 
'分块进程的进程ID '         =   str(   blocked,   2   ), 
'工作站名称 '                     =   convert(   char(10),   hostname   ), 
'执行命令的用户 '             =   convert(   char(10),   suser_name(   uid   )   ), 
'数据库名 '                         =   convert(   char(10),   db_name(   dbid   )   ),               
'应用程序名 '                     =   convert(   char(10),   program_name   ), 
'正在执行的命令 '             =   convert(   char(16),   cmd   ), 
'累计CPU时间 '                   =   str(   cpu,   7   ), 
'IO '                                     =   str(   physical_io,   7   ), 
                '登录名 '                             =   loginame 
from   master..sysprocesses 
where   spid   in   (   select   blocked   from   master..sysprocesses   ) 
and   blocked   =   0 
and   (db_name( dbid)   =   'trasen_db '   )
order   by   str(spid,4)

 

--抛弃的进程

select   
'进程ID[SPID] '                 =   str(   spid,   4   ), 
'进程ID状态 '                     =   convert(   char(10),   status   ), 

'分块进程的进程ID '         =   str(   blocked,   2   ), 
'工作站名称 '                     =   convert(   char(10),   hostname   ), 
'执行命令的用户 '             =   convert(   char(10),   suser_name(   uid   )   ), 
'数据库名 '                         =   convert(   char(10),   db_name(   dbid   )   ),               
'应用程序名 '                     =   convert(   char(10),   program_name   ), 
'正在执行的命令 '             =   convert(   char(16),   cmd   ), 
'累计CPU时间 '                   =   str(   cpu,   7   ), 
'IO '                                     =   str(   physical_io,   7   ), 
                '登录名 '                             =   loginame 
from   master..sysprocesses 
where   blocked   <>   0   
and   (db_name( dbid)   =   'trasen_db '   )
order   by   spid 


 

 

SET QUOTED_IDENTIFIER ON 

GO
SET ANSI_NULLS ON 
GO

 
CREATE PROCEDURE sp_who_lock

AS