create
PROCEDURE
[
dbo
]
.
[
auto_checkblocks
]
AS
set
nocount
on
if
exists
(
select
*
from
master..sysprocesses
where
blocked
<>
0
)
begin
/**/
/* show top blockers, but no duplicates */
select
'
请尝试使用KILL [SPID] 来杀进程
'
--
select '请尝试使用SP_LOCK [SPID]来显示锁信息，用OBJECT_NAME(ID)来显示锁对象名称或用sp_who [SPID] 来显示信息'
--
select '在使用OBJECT_NAME显示对象名称时请注意对应的db_id'
select
'
以下是引起阻塞的语句
'
select
distinct
'
进程ID
'
=
str
( a.spid,
4
),
'
进程ID状态
'
=
convert
(
char
(
20
), a.status ),
'
分块进程的进程ID
'
=
str
( a.blocked,
2
),
'
工作站名称
'
=
convert
(
char
(4
0
), a.hostname ),
'
执行命令的用户
'
=
convert
(
char
(2
0
),
suser_name
( a.uid ) ),
'
数据库名
'
=
convert
(
char
(2
0
),
db_name
(a.dbid ) ),
'
应用程序名
'
=
convert
(
char
(3
0
), a.program_name ),
'
正在执行的命令
'
=
convert
(
char
(2
6
), a.cmd ),
'
累计CPU时间
'
=
str
( a.cpu,
7
),
'
IO
'
=
str
( a.physical_io,
7
),
'
登录名
'
=
a.loginame,
'
执行语句
'
=
b.
text
from
master..sysprocesses a
cross
apply sys.dm_exec_sql_text(a.sql_handle) b
where
spid
in
(
select
blocked
from
master..sysprocesses )
and
blocked
=
0
order
by
str
(spid,
4
)
/**/
/* 显示阻塞牺牲品 */
select
'
以下是被阻塞的等待执行的语句
'
select
'
进程ID[SPID]
'
=
str
( a.spid,
4
),
'
进程ID状态
'
=
convert
(
char
(2
0
), a.status ),
'
分块进程的进程ID
'
=
str
( a.blocked,
2
),
'
工作站名称
'
=
convert
(
char
(4
0
), a.hostname ),
'
执行命令的用户
'
=
convert
(
char
(
10
),
suser_name
( a.uid ) ),
'
数据库名
'
=
convert
(
char
(
10
),
db_name
( a.dbid ) ),
'
应用程序名
'
=
convert
(
char
(2
0
), a.program_name ),
'
正在执行的命令
'
=
convert
(
char
(
16
), a.cmd ),
'
累计CPU时间
'
=
str
( a.cpu,
7
),
'
IO
'
=
str
( a.physical_io,
7
),
'
登录名
'
=
a.loginame,
'
执行语句
'
=
b.
text
from
master..sysprocesses a
cross
apply sys.dm_exec_sql_text(a.sql_handle) b
where
blocked
<>
0
order
by
spid
end
else
begin
select
'
恭喜！当前没有阻塞，当前的进程信息如下.
'
,
convert
(
char
(
24
),
getdate
(),
13
)
select
'
进程ID
'
=
str
( spid,
4
),
'
进程ID状态
'
=
convert
(
char
(2
0
), status ),
'
分块进程的进程ID
'
=
str
( blocked,
2
),
'
工作站名称
'
=
convert
(
char
(4
0
), hostname ),
'
执行命令的用户
'
=
convert
(
char
(
10
),
suser_name
( uid ) ),
'
数据库名
'
=
convert
(
char
(
10
),
db_name
( dbid ) ),
'
应用程序名
'
=
convert
(
char
(2
0
), program_name ),
'
正在执行的命令
'
=
convert
(
char
(2
6
), cmd ),
'
累计CPU时间
'
=
str
( cpu,
7
),
'
IO
'
=
str
( physical_io,
7
),
'
登录名
'
=
loginame
from
master..sysprocesses
where
blocked
=
0
order
by
spid
end
return
