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)
以上方法都是网上汇总。