SQL Check Block SP

  /****** Object:  StoredProcedure [dbo].[auto_checkblocks]    Script Date: 04/05/2013 11:43:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER 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 N'请尝试使用KILL [SPID] 来杀进程'   --      select '请尝试使用SP_LOCK [SPID]来显示锁信息，用OBJECT_NAME(ID)来显示锁对象名称或用sp_who [SPID] 来显示信息'    --     select '在使用OBJECT_NAME显示对象名称时请注意对应的db_id'         select N'以下是引起阻塞的语句'     select distinct     '进程ID'              = str( a.spid, 4 ),     '进程ID状态'          = convert( char(10), a.status ),     a.login_time,     a.last_batch,     '分块进程的进程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 N'以下是被阻塞的等待执行的语句'         select     '进程ID[SPID]'        = str( a.spid, 4 ),     '进程ID状态'          = convert( char(10), a.status ),     a.login_time,     a.last_batch,     '分块进程的进程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 N'恭喜！当前没有阻塞，当前的进程信息如下.', convert (char(24),GETDATE(),13)         select     '进程ID'              = str( spid, 4 ),     '进程ID状态'          = convert( char(10), status ),      login_time,      last_batch,     '分块进程的进程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