引起死锁的进程查看：它没有被阻塞，但别的进程被它阻塞着
   select * from sysprocesses a where  a.blocked=0
   AND  EXISTS( select * from sysprocesses b where  b.blocked>0  and b.blocked = a.spid   )

*--处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

--*/
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER PROC [dbo].[p_lockinfo]
    @kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
    @show_spid_if_nolock BIT = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
    DECLARE @count INT ,
        @s VARCHAR(MAX) ,
        @i INT
    SELECT  id = IDENTITY( INT,1,1 ),
            标志 ,
            进程ID = spid ,
            线程ID = kpid ,
            块进程ID = blocked ,
            数据库ID = dbid ,
            数据库名 = DB_NAME(dbid) ,
            用户ID = uid ,
            用户名 = loginame ,
            累计CPU时间 = cpu ,
            登陆时间 = login_time ,
            打开事务数 = open_tran ,
            进程状态 = status ,
            工作站名 = hostname ,
            应用程序名 = program_name ,
            工作站进程ID = hostprocess ,
            域名 = nt_domain ,
            网卡地址 = net_address,
sql_handle,
stmt_start,
stmt_end

    INTO    #t
    FROM    ( SELECT    标志 = '死锁的进程' ,
                        spid ,
                        kpid ,
                        a.blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = a.spid ,
                        s2 = 0,
sql_handle,
stmt_start,
stmt_end
              FROM      master..sysprocesses a
                        JOIN ( SELECT   blocked
                               FROM     master..sysprocesses
                               GROUP BY blocked
                             ) b ON a.spid = b.blocked
              WHERE     a.blocked = 0
              UNION ALL
              SELECT    '|_牺牲品_>' ,
                        spid ,
                        kpid ,
                        blocked ,
                        dbid ,
                        uid ,
                        loginame ,
                        cpu ,
                        login_time ,
                        open_tran ,
                        status ,
                        hostname ,
                        program_name ,
                        hostprocess ,
                        nt_domain ,
                        net_address ,
                        s1 = blocked ,
                        s2 = 1,
sql_handle,
stmt_start,
stmt_end
              FROM      master..sysprocesses a
              WHERE     blocked <> 0
            ) a
    ORDER BY s1 ,
            s2

    SELECT  @count = @@rowcount ,
            @i = 1

    IF @count = 0
        AND @show_spid_if_nolock = 1
        BEGIN
            INSERT  #t
                    SELECT  标志 = '正常的进程' ,
                            spid ,
                            kpid ,
                            blocked ,
                            dbid ,
                            DB_NAME(dbid) ,
                            uid ,
                            loginame ,
                            cpu ,
                            login_time ,
                            open_tran ,
                            status ,
                            hostname ,
                            program_name ,
                            hostprocess ,
                            nt_domain ,
                            net_address,
sql_handle,
stmt_start,
stmt_end
                    FROM    master..sysprocesses
            SET @count = @@rowcount
        END

    IF @count > 0
        BEGIN
            CREATE TABLE #t1
                (
                  id INT IDENTITY(1, 1) ,
                  a NVARCHAR(30) ,
                  b INT ,
                  EventInfo NVARCHAR(255)
                )
            IF @kill_lock_spid = 1
                BEGIN
                    DECLARE @spid VARCHAR(10) ,
                        @标志 VARCHAR(10)
                    WHILE @i <= @count
                        BEGIN
                            SELECT  @spid = 进程ID ,
                                    @标志 = 标志
                            FROM    #t
                            WHERE   id = @i
                            INSERT  #t1
                                    EXEC ( 'dbcc inputbuffer(' + @spid + ')'
                                        )
                            IF @标志 = '死锁的进程'
                                EXEC('kill '+@spid)
                            SET @i = @i + 1
                        END
                END
            ELSE
                WHILE @i <= @count
                    BEGIN
                        SELECT  @s = 'dbcc inputbuffer('
                                + CAST(进程ID AS VARCHAR) + ')'
                        FROM    #t
                        WHERE   id = @i
                        INSERT  #t1
                                EXEC ( @s
                                    )
                        SET @i = @i + 1
                    END
            SELECT  标志 ,
数据库名 ,
 进程的SQL语句 = b.EventInfo,
sqlText=substring(qt.text,a.stmt_start/2,
  (case when a.stmt_end = -1
 then len(convert(nvarchar(max), qt.text)) * 2
 else a.stmt_end end -a.stmt_start)/2
) ,
进程状态,
            用户名,
            累计CPU时间,
            登陆时间  ,
            工作站名 ,
            应用程序名,
进程ID ,
            线程ID
            FROM    #t a
                    JOIN #t1 b ON a.id = b.id
OUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt
        END
GO

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。