查看数据库死锁的进程及清除sleeping进程（Kill__Sleeping_Processes）

 
例28.49  查看数据库死锁的进程（proc_who_lock）

用proc_ find _lock过程查看数据库死锁的进程。

if exists (SELECT *
           FROM dbo.sysobjects
           WHERE id = object_id(N'[dbo].[proc_find_lock]')
           and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_find_lock]

GO

create procedure proc_find_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
         @intRowcount int,
         @intCountProperties int,
         @intCounter int

create table #temp_lock (
id int identity(1,1),
spid smallint,
bl smallint)

IF @@ERROR<>0 RETURN @@ERROR
   INSERT INTO #temp_lock(spid,bl)
   SELECT   0 ,blocked
   FROM (SELECT * FROM sysprocesses WHERE   blocked>0 ) a
   WHERE not exists(SELECT *
   FROM (SELECT *   FROM sysprocesses WHERE   blocked>0 ) b  WHERE a.blocked=spid)
   union
   SELECT spid,blocked FROM sysprocesses  WHERE   blocked>0
  
IF @@ERROR<>0 RETURN @@ERROR
   SELECT @intCountProperties = Count(*),@intCounter = 1  --找到临时表的记录数
   FROM #temp_lock

IF @@ERROR<>0 RETURN @@ERROR
   if @intCountProperties=0
      SELECT '没有阻塞和死锁信息' as 提示

while @intCounter <= @intCountProperties
BEGIN

SELECT @spid = spid,@bl = bl
FROM #temp_lock WHERE Id = @intCounter

begin

if @spid =0

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

           + ',其执行的SQL语句为：'

else

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

           + '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10))

           +'阻塞,其当前进程执行的SQL语句为：'

DBCC INPUTBUFFER (@bl )

end

set @intCounter = @intCounter + 1

end

drop table #temp_lock

return 0

end

运行过程proc_find_lock，结果如图28-48所示。

proc_find_lock

 

例28.46  清除sleeping进程（Kill__Sleeping_Processes）

Sleeping进程是系统中处于睡眠状态尚未活动的进程，这部分进程可以杀掉。

CREATE PROCEDURE dbo.Kill__Sleeping_Processes @dbname varchar(50)

AS

BEGIN

SET NOCOUNT ON

DECLARE @spid INT,

@cnt INT,

@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

WHILE @spid IS NOT NULL

BEGIN

SET @sql = 'KILL ' + RTRIM(@spid)

EXEC ( @sql )

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

END

END

GO

运行改存储过程后杀掉sleeping进程：

Kill__Sleeping_Processes SQLS2008

  清除前后可以用EXEC sp_who 'active '进行查看。