SqlServer如何查看死锁的进程

查看当前死锁

SELECT
    request_session_id spid,
    OBJECT_NAME(
        resource_associated_entity_id
    ) tableName
FROM
    sys.dm_tran_locks
WHERE
    resource_type = 'OBJECT'

 

查看死锁进程

SELECT * FROM sys.dm_exec_connections;
SELECT * FROM sys.dm_exec_sessions;

SELECT spid
, blocked
, DB_NAME (sp.dbid) AS DBName
, program_name
, waitresource
, lastwaittype
, sp.loginame
, sp.hostname
, a.[Text] AS [TextData]
, SUBSTRING (A. TEXT, sp.stmt_start / 2, (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp
OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50
ORDER BY blocked DESC, DB_NAME (sp.dbid) ASC, a.[text]

 

 

杀掉死锁进程

kill spid

 

批量杀死锁

DECLARE @SQL VARCHAR(MAX);
SET @SQL=''
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
FROM [sys].[sysprocesses]
WHERE dbid=DB_ID('Scm');

exec(@SQL);



select * from [sys].[sysprocesses] where dbid = db_id('Scm')