Alek 's Blog

<< SQL SERVER日志清除的方法 | 首 页 | 张瑞敏语录 >> 2007-07-30 SQL Server进程阻塞的检查和解决办法 - [ 数据库 ] 版权声明 :转载时请以超链接形式标明文章原始出处和作者信息及 本声明 http://alek.blogbus.com/logs/7216063.html create procedure sp_who_lock as 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 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 #tmp_lock_who IF @@ERROR <> 0 RETURN @@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 ( @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 #tmp_lock_who return 0 end GO -- 结束SQL阻塞的进程%%%%%%%%%%%%%%%%%%%%%% create procedure sp_Kill_lockProcess as begin SET NOCOUNT ON declare @spid int , @bl int , @intTransactionCountOnEntry int , @intRowcount int , @intCountProperties int , @intCounter int , @sSql nvarchar ( 200 ) 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 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 #tmp_lock_who IF @@ERROR <> 0 RETURN @@ERROR while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid, @bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid = 0 begin set @sSql = ' kill ' + CAST ( @bl AS VARCHAR ( 10 )) exec sp_executesql @sSql end end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who SET NOCOUNT OFF return 0 end GO 相关文章: SQL SERVER日志清除的方法 2007-07-30 SQL编写规范 2007-07-24 用SQL语句去除字段中的回车键 2007-07-21 SQL安装错误"以前的某个程序安装已在安装计算机上创建挂起的文件操作。运行安装程序之前必须重新启动计算机" 2007-05-02 SQL Server各种日期计算方法 2007-04-13 收藏到: Del.icio.us Tag: 引用地址: alek 发表于 15:43:43 | 编辑 | 继续话题 | 转发 | 推荐 发表评论

原始快照:web.archive.org