select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作
from master..sysprocesses a,master..sysprocesses b
where a.blocked<>0 and a.blocked= b.spid
查询死锁:
SELECT DISTINCT dtl.request_session_id,OBJECT_NAME(dtl.resource_associated_entity_id)
FROM sys.dm_tran_locks dtl WHERE dtl.resource_type='OBJECT'
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC
查询死锁：
select spid,status,lastwaittype,program_name
from master..sysprocesses
where db_name(dbid) = 'dbname'
and spid<>@@spid
and dbid<>0
