Create Proc [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(10), A.Status ),
'分块进程的进程ID' = Str( A.Blocked, 2 ),
'工作站名称' = Convert( Char(10), A.Hostname ),
'执行命令的用户' = Convert( Char(10), Suser_Name( A.Uid ) ),
'数据库名' = Convert( Char(10), Db_Name(A.Dbid ) ),
'应用程序名' = Convert( Char(10), 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 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(10), A.Status ),
'分块进程的进程ID' = Str( A.Blocked, 2 ),
'工作站名称' = Convert( Char(10), A.Hostname ),
'执行命令的用户' = Convert( Char(10), Suser_Name( A.Uid ) ),
'数据库名' = Convert( Char(10), Db_Name( A.Dbid ) ),
'应用程序名' = Convert( Char(10), 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(10), Status ),
'分块进程的进程ID' = Str( Blocked, 2 ),
'工作站名称' = Convert( Char(10), Hostname ),
'执行命令的用户' = Convert( Char(10), Suser_Name( Uid ) ),
'数据库名' = Convert( Char(10), Db_Name( Dbid ) ),
'应用程序名' = Convert( Char(10), Program_Name ),
'正在执行的命令' = Convert( Char(16), Cmd ),
'累计CPU时间' = Str( Cpu, 7 ),
'IO' = Str( Physical_Io, 7 ),
'登录名' = Loginame
From Master..Sysprocesses
Where Blocked = 0
Order By Spid
End
Return
Go
