p_fetchlockmsg.sql

快捷获取SQLSERVER进程锁信息

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[P_FETCHLOCKMSG]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
    DROP PROCEDURE [DBO].[P_FETCHLOCKMSG]
GO
/*************************************************************************************
名称 P_FETCHLOCKMSG
概要说明
    中文名称：检取数据库死锁信息
    用    途: 用于查询发生死锁的进程,数据库,表及操作SQL语句
    数 据 库：
语法信息
    输入参数：
        
      
    输出参数：
       
    调用举例:
			EXEC P_FETCHLOCKMSG
        
外部联系
    上级调用：
    下级调用： 
    输 入 表：
    输 出 表：无
功能修订
    简要说明：如果为表则返回表结构,如果是过程则返回过程结构

    修订记录：     
			2007-08-14	林加在	创建
************************************************************************************ */
GO
CREATE PROCEDURE P_FETCHLOCKMSG
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON
    --建立进程表
    IF OBJECT_ID('#TABLE_WHO') IS NOT NULL DROP TABLE #TABLE_WHO

    CREATE TABLE #TABLE_WHO(
        SPID INTEGER,
        ECID INTEGER,
        STATUS VARCHAR(20),
        LOGINNAME VARCHAR(50),
        HOSTNAME VARCHAR(50),
        BLK INTEGER,
        DBNAME VARCHAR(50),
        CMD VARCHAR(40)
    )

    --生成进程数据
    INSERT INTO #TABLE_WHO EXEC SP_WHO

    --查出死锁进程
    --SELECT * FROM #TABLE_WHO WHERE BLK>0

    --找出发生锁定的数据库及表
    IF OBJECT_ID('#TABLE_LOCK') IS NOT NULL DROP TABLE #TABLE_LOCK
    CREATE TABLE #TABLE_LOCK(
        SPID INTEGER,
        DBID INTEGER,
        OBJID INTEGER,
        INTID INTEGER,
        TYPE VARCHAR(10),
        RESOURCE VARCHAR(50),
        MODE VARCHAR(10),
        STATUS VARCHAR(20)
    )
    INSERT INTO #TABLE_LOCK EXEC SP_LOCK

    IF OBJECT_ID('#TMP_TABLENAME') IS NOT NULL DROP TABLE #TMP_TABLENAME
    CREATE TABLE #TMP_TABLENAME(
        TABLENAME VARCHAR(40)
    )
    IF OBJECT_ID('#TMP_INPUTSQL') IS NOT NULL DROP TABLE #TMP_INPUTSQL
    CREATE TABLE #TMP_INPUTSQL(
        EVENTTYPE VARCHAR(40),
        PARAMETERS INTEGER,
        EVENTINFO VARCHAR(5000)
    )
    DECLARE @SPID INTEGER,@BLK INT,@DBID INT,@OBJID INT,@TABLE VARCHAR(40),@DBNAME VARCHAR(30),@SQL VARCHAR(5000),@BLK_SQL VARCHAR(5000)

    SELECT TOP 0 @DBNAME 被锁数据库,@TABLE 被锁表名,@SPID 被锁进程号,@SQL 被锁操作语句,@BLK 主锁进程号,@BLK_SQL 主锁操作未次语句,0 是否为锁根源进程 INTO #TMP_RESULT
    DECLARE CURROW CURSOR LOCAL FOR SELECT SPID,BLK FROM #TABLE_WHO WHERE BLK>0
    OPEN CURROW
    FETCH CURROW INTO @SPID,@BLK
    WHILE @@FETCH_STATUS=0
    BEGIN
        SELECT @DBID=DBID,@OBJID=OBJID FROM #TABLE_LOCK WHERE STATUS='WAIT' AND SPID=@SPID
        SELECT @DBNAME=NAME FROM MASTER..SYSDATABASES WHERE DBID=@DBID
        
        TRUNCATE TABLE #TMP_TABLENAME
        EXEC ('INSERT INTO #TMP_TABLENAME SELECT NAME FROM '+@DBNAME+'..SYSOBJECTS WHERE ID='+@OBJID)
        SELECT @TABLE=TABLENAME FROM #TMP_TABLENAME
        
        
        TRUNCATE TABLE #TMP_INPUTSQL
        INSERT INTO #TMP_INPUTSQL EXEC ('DBCC INPUTBUFFER('+@SPID+')')
        SELECT @SQL=EVENTINFO FROM #TMP_INPUTSQL
        TRUNCATE TABLE #TMP_INPUTSQL
        INSERT INTO #TMP_INPUTSQL EXEC ('DBCC INPUTBUFFER('+@BLK+')')
        SELECT @BLK_SQL=EVENTINFO FROM #TMP_INPUTSQL

        PRINT '**********被锁数据库**********' + CHAR(10)+@DBNAME
        PRINT '***********被锁表名***********' + CHAR(10) + @TABLE
        PRINT '**********被锁进程号**********' + CHAR(10) + CONVERT(VARCHAR(10),@SPID)
        PRINT '*********被锁操作语句*********' + CHAR(10) + @SQL
        PRINT '**********主锁进程号**********' + CHAR(10) + CONVERT(VARCHAR(10),@BLK)
        PRINT '*********主锁操作语句*********' + CHAR(10) + @BLK_SQL

        INSERT INTO #TMP_RESULT
        SELECT @DBNAME 被锁数据库,@TABLE 被锁表名,@SPID 被锁进程号,@SQL 被锁操作语句,@BLK 主锁进程号,@BLK_SQL 主锁操作未次语句,0
        FETCH CURROW INTO @SPID,@BLK
    END
    update #TMP_RESULT
    set 是否为锁根源进程=1
    where 主锁进程号 not in (select 被锁进程号 from #TMP_RESULT)

    SELECT * FROM #TMP_RESULT order by 主锁进程号,被锁进程号

    DROP TABLE #TABLE_WHO
    DROP TABLE #TABLE_LOCK
    DROP TABLE #TMP_INPUTSQL
    DROP TABLE #TMP_TABLENAME
    DROP TABLE #TMP_RESULT
    SET NOCOUNT OFF
END
GO