sql server 查询死锁进程及死锁检测

use master  
go  
  
--检索死锁进程  
select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name  
from sysprocesses  
where spid in  
( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0)  


--死锁检测 
use master 
Select * from sysprocesses where blocked<>0 
--找到SPID   
exec sp_lock 
--根据SPID找到OBJID 
select object_name(674101442) 
--根据OBJID找到表名


--查看当前数据库服务器的所有链接
select * from openrowset('SQLOLEDB','192.0.0.1';'sa';'P@sss','sp_who')


--查看被锁表
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   from   sys.dm_tran_locks where resource_type='OBJECT' 
--ORder BY request_session_id 
--spid   锁表进程 tableName   被锁表名 解锁： 
declare @spid  int Set @spid  = 75 
--锁表进程
declare @sql varchar(1000)set @sql='kill '+cast(@spid  as varchar)
exec(@sql)

 

以上方法都是网上汇总。