SqlServer 查看死結資訊

SELECT 0 ,blocked
FROM (SELECT * FROM sys.sysprocesses WHERE blocked>0 ) a
WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM sys.sysprocesses WHERE blocked>0 ) b
WHERE a.blocked=spid)
UNION SELECT spid,blocked FROM sys.sysprocesses WHERE blocked>0

使用查詢後的進程ID可以查看死結元兇的Sql語句和被犧牲Sql語句。

DBCC INPUTBUFFER(82)

附人為製造死結
BEGIN TRANSACTION
SELECT * FROM 表名 WITH (TABLOCKX)
WAITFOR DELAY '00:05:00 '
COMMIT TRANSACTION