Sql Server2005 查询死锁进程和抛弃进程

来源：互联网发布：qq群淘宝客没有订单编辑：程序博客网时间：2024/05/18 01:06

isale为数据库名称

--引起死锁(阻塞)的进程(阻塞源头)
--kill源头的进程ID,一般可解决阻塞
select   distinct
'进程ID '                             =   str(   spid,   4   ),
'进程ID状态 '                     =   convert(   char(10),   status   ),
'分块进程的进程ID '         =   str(   blocked,   2   ),
'工作站名称 '                     =   convert(   char(10),   hostname   ),
'执行命令的用户 '             =   convert(   char(10),   suser_name(   uid   )   ),
'数据库名 '                         =   convert(   char(10),   db_name(   dbid   )   ),
'应用程序名 '                     =   convert(   char(10),   program_name   ),
'正在执行的命令 '             =   convert(   char(16),   cmd   ),
'累计CPU时间 '                   =   str(   cpu,   7   ),
'IO '                                     =   str(   physical_io,   7   ),
                '登录名 '                             =   loginame
from   master..sysprocesses
where   spid   in   (   select   blocked   from   master..sysprocesses   )
and   blocked   =   0
and   (db_name( dbid)   =   'isale '   )
order   by   str(spid,4)

抛弃的进程

select
'进程ID[SPID] '                 =   str(   spid,   4   ),
'进程ID状态 '                     =   convert(   char(10),   status   ),
'分块进程的进程ID '         =   str(   blocked,   2   ),
'工作站名称 '                     =   convert(   char(10),   hostname   ),
'执行命令的用户 '             =   convert(   char(10),   suser_name(   uid   )   ),
'数据库名 '                         =   convert(   char(10),   db_name(   dbid   )   ),
'应用程序名 '                     =   convert(   char(10),   program_name   ),
'正在执行的命令 '             =   convert(   char(16),   cmd   ),
'累计CPU时间 '                   =   str(   cpu,   7   ),
'IO '                                     =   str(   physical_io,   7   ),
                '登录名 '                             =   loginame
from   master..sysprocesses
where   blocked   <>   0
and   (db_name( dbid)   =   'isale '   )
order   by   spid

SET QUOTED_IDENTIFIER ON

GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_who_lock

BEGIN

DECLARE @spid INT ,

@bl INT ,

@intTransactionCountOnEntry INT ,

@intRowcount INT ,

@intCountProperties INT ,

@intCounter INT

CREATE TABLE #tmp_lock_who

(

id INT IDENTITY(1, 1) ,

spid SMALLINT ,

bl SMALLINT

)

IF @@ERROR <> 0

RETURN @@ERROR

INSERT INTO #tmp_lock_who ( spid, bl )

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

IF @@ERROR <> 0

RETURN @@ERROR

-- 找到临时表的记录数

SELECT @intCountProperties = COUNT(*), @intCounter = 1

FROM #tmp_lock_who

IF @@ERROR <> 0

RETURN @@ERROR

IF @intCountProperties = 0

SELECT N'现在没有阻塞和死锁信息' AS message

-- 循环开始

WHILE @intCounter <= @intCountProperties

BEGIN

-- 取第一条记录

SELECT @spid = spid, @bl = bl

FROM #tmp_lock_who

WHERE Id = @intCounter

BEGIN

IF @spid = 0

SELECT N'引起数据库死锁的是: ' + CAST(@bl AS VARCHAR(10))

+ N'进程号,其执行的SQL语法如下'

ELSE

SELECT N'进程号SPID：' + CAST(@spid AS VARCHAR(10))

+ N'被进程号SPID：' + CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'

DBCC INPUTBUFFER (@bl )

END

-- 循环指针下移

SET @intCounter = @intCounter + 1

END

DROP TABLE #tmp_lock_who

RETURN 0

END

EXEC sp_who_lock

DROP PROC sp_who_lock

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON