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(40), a.hostname ),
'执行命令的用户' = convert( char(20), suser_name( a.uid ) ),
'数据库名' = convert( char(20), db_name(a.dbid ) ),
'应用程序名' = convert( char(30), a.program_name ),
'正在执行的命令' = convert( char(26), 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(20), a.status ),
'分块进程的进程ID' = str( a.blocked, 2 ),
'工作站名称' = convert( char(40), a.hostname ),
'执行命令的用户' = convert( char(10), suser_name( a.uid ) ),
'数据库名' = convert( char(10), db_name( a.dbid ) ),
'应用程序名' = convert( char(20), 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(20), status ),
'分块进程的进程ID' = str( blocked, 2 ),
'工作站名称' = convert( char(40), hostname ),
'执行命令的用户' = convert( char(10), suser_name( uid ) ),
'数据库名' = convert( char(10), db_name( dbid ) ),
'应用程序名' = convert( char(20), program_name ),
'正在执行的命令' = convert( char(26), cmd ),
'累计CPU时间' = str( cpu, 7 ),
'IO' = str( physical_io, 7 ),
'登录名' = loginame
from master..sysprocesses
where blocked = 0
order by spid
end
return
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(40), a.hostname ),
'执行命令的用户' = convert( char(20), suser_name( a.uid ) ),
'数据库名' = convert( char(20), db_name(a.dbid ) ),
'应用程序名' = convert( char(30), a.program_name ),
'正在执行的命令' = convert( char(26), 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(20), a.status ),
'分块进程的进程ID' = str( a.blocked, 2 ),
'工作站名称' = convert( char(40), a.hostname ),
'执行命令的用户' = convert( char(10), suser_name( a.uid ) ),
'数据库名' = convert( char(10), db_name( a.dbid ) ),
'应用程序名' = convert( char(20), 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(20), status ),
'分块进程的进程ID' = str( blocked, 2 ),
'工作站名称' = convert( char(40), hostname ),
'执行命令的用户' = convert( char(10), suser_name( uid ) ),
'数据库名' = convert( char(10), db_name( dbid ) ),
'应用程序名' = convert( char(20), program_name ),
'正在执行的命令' = convert( char(26), cmd ),
'累计CPU时间' = str( cpu, 7 ),
'IO' = str( physical_io, 7 ),
'登录名' = loginame
from master..sysprocesses
where blocked = 0
order by spid
end
return