use master go createprocedure sp_who_lock as begin declare@spidint,@blint, @intTransactionCountOnEntryint, @intRowcountint, @intCountPropertiesint, @intCounterint createtable #tmp_lock_who ( id intidentity(1,1), spid smallint, bl smallint) IF@@ERROR<>0RETURN@@ERROR insertinto #tmp_lock_who(spid,bl) select0 ,blocked from (select*from sysprocesses where blocked>0 ) a wherenotexists(select*from (select*from sysprocesses where blocked>0 ) b where a.blocked=spid) unionselect spid,blocked from sysprocesses where blocked>0 IF@@ERROR<>0RETURN@@ERROR -- 找到临时表的记录数 select@intCountProperties=Count(*),@intCounter=1 from #tmp_lock_who IF@@ERROR<>0RETURN@@ERROR if@intCountProperties=0 select'现在没有阻塞和死锁信息'as message -- 循环开始 while@intCounter<=@intCountProperties begin -- 取第一条记录 select@spid= spid,@bl= bl from #tmp_lock_who where Id =@intCounter begin if@spid=0 select'引起数据库死锁的是: '+CAST(@blASVARCHAR(10)) +'进程号,其执行的SQL语法如下' else select'进程号SPID:'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID:'+CAST(@blASVARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set@intCounter=@intCounter+1 end droptable #tmp_lock_who return0 end
use master go ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_killspid]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[p_killspid] GO createproc p_killspid @dbnamevarchar(200) --要关闭进程的数据库名 as declare@sqlnvarchar(500) declare@spidnvarchar(20) declare #tb cursorfor select spid=cast(spid asvarchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #tb fetchnextfrom #tb into@spid while@@fetch_status=0 begin exec('kill '+@spid) fetchnextfrom #tb into@spid end close #tb deallocate #tb go --用法 exec p_killspid 'newdbpy'