use master
go
createprocedure sp_who_lock
asbegindeclare@spidint,@blint,@intTransactionCountOnEntryint,@intRowcountint,@intCountPropertiesint,@intCounterintcreatetable#tmp_lock_who (
id intidentity(1,1),
spid smallint,
bl smallint)IF @@ERROR<>0RETURN @@ERRORinsertinto#tmp_lock_who(spid,bl) select 0 ,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>0IF @@ERROR<>0RETURN @@ERROR-- 找到临时表的记录数 select@intCountProperties=Count(*),@intCounter=1from#tmp_lock_who IF @@ERROR<>0RETURN @@ERRORif@intCountProperties=0select'现在没有阻塞和死锁信息'as message
-- 循环开始 while@intCounter<=@intCountPropertiesbegin-- 取第一条记录 select@spid= spid,@bl= bl
from#tmp_lock_who where Id = @intCounter beginif@spid=0select'引起数据库死锁的是: '+ CAST(@blASVARCHAR(10))+'进程号,其执行的SQL语法如下'elseselect'进程号SPID:'+ CAST(@spidASVARCHAR(10))+'被'+'进程号SPID:'+ CAST(@blASVARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER (@bl)end-- 循环指针下移 set@intCounter=@intCounter+1enddroptable#tmp_lock_who return0end
创建删除指定数据库的死锁的存储过程
1.use master
2. go
3.4.ifexists(select*from dbo.sysobjects where id = object_id(N‘[dbo].[p_killspid]‘)and OBJECTPROPERTY(id, N‘IsProcedure‘)=1)5.dropprocedure[dbo].[p_killspid]6. GO
7.8.createproc p_killspid
9.@dbnamevarchar(200)--要关闭进程的数据库名 10.as11.declare@sql nvarchar(500)12.declare@spid nvarchar(20)13.14.declare#tb cursor for 15.select spid=cast(spid asvarchar(20))from master..sysprocesses where dbid=db_id(@dbname)16.open#tb 17.fetchnextfrom#tb into @spid 18.while @@fetch_status=019.begin20.exec(‘kill ‘[email protected])21.fetchnextfrom#tb into @spid 22.end23.close#tb 24.deallocate#tb 25. go