Alek 's Blog
alek · 2007-07-30
<< 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