LockKill.sql

-- =============================================
-- Author:      Mike Deng
-- Create date: 2014/05/14
-- Description: 杀掉死锁进程
-- =============================================

DECLARE    @toKill BIT = 0  --0 或 NULL 只显示， 1.杀掉

BEGIN
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @fromSql NVARCHAR(MAX);

    -- 查询出所有死锁和与被阻塞的进程
    SET @fromSql = '
    DECLARE @cmd NVARCHAR(MAX);
    ;WITH CTE as(
          SELECT DISTINCT Type = ''死锁的进程''
            , a.spid
            , blockId = 0
            , DBName = DB_NAME(a.dbid)
            , a.HostName
            , ProgramName = a.Program_Name
            , Dead = 1
          FROM SYS.SYSPROCESSES a JOIN 
                SYS.SYSPROCESSES as b ON a.spid = b.blocked
                      WHERE a.blocked = 0
            UNION ALL
        SELECT DISTINCT  Type = ''被阻塞的进程''
            , a.spid
            , blockId = a.blocked
            , DBName = DB_NAME(a.dbid)
            , a.HostName
            , ProgramName = a.Program_Name
            , Dead = 0
        FROM SYS.SYSPROCESSES a WHERE blocked <> 0
    )  '

    -- 根据传入的值，决定是否删除
    IF(ISNULL(@toKill, 0) = 1)  --如果等于 1，则杀掉
        BEGIN
            SET @sql = @fromSql
                + 'SELECT @cmd =  (
                        SELECT  ('' KILL '' + LTRIM(str(spid))) FROM cte WHERE dead = 1
                            FOR XML PATH('''')
                    )

                    IF(ISNULL(@cmd, '''') <> '''')
                        BEGIN
                            EXEC (@cmd);
                            PRINT ''已执行kill死锁命令【'' + @cmd + ''】， 锁已解除'';
                        END 
                    ELSE IF(@@RowCount = 0)
                        BEGIN
                            PRINT ''未发现死锁, 不能杀掉'';
                        END'
        END
    ELSE
        BEGIN   -- 显示所有死锁与被阻塞的进程
            SET @sql = @fromSql + ', cte2 AS( SELECT * FROM cte WHERE Dead = 1
                                                    UNION ALL
                                              SELECT k.* FROM cte AS k JOIN 
                                                cte2 AS p ON k.blockId = p.spid
                                        )
                                        SELECT * FROM cte2 ORDER BY blockId';
        END

    EXEC SP_EXECUTESQL @sql
END