自动杀阻塞进程

 

由于企业生产环境中经常有数据库进程相互阻塞，导致业务程序停止响应，为了快速解决这个问题，将故障消灭在萌芽状态，故开发了这个sp。

这个sp调用了几个子sp：

1,sp_block 这个请参考我的其他博客文章；
2,sp_send_SMS 这是个比较简单的sp，主要是往一个短信队列表写数据，这个大家可以自己创建。
3,sp_send_mail 这是一个发送邮件的sp，在互联网上可以找到很多这方面的代码，有时间我再上传一个。

 

说明：

 1,这个sp统计进程阻塞的情况，找到阻塞其他进程个数最多的那个，将其kill。
 2,不会kill系统进程。注意：复制分发进程不是系统进程，可能会被kill。
 3,可能会kill正在运行的进程。最初的设计不允许这样做，后来根据业务经验修改成这样子的。
 4,会创建一个表记录被kill的那个进程的信息，包括SQL和执行计划等，方便后续查找原因。

 

 

use master GO if object_id('dbo.sp_block_AutoKill') is not null drop procedure dbo.sp_block_AutoKill GO create procedure dbo.sp_block_AutoKill @SMS_TO varchar(1000)='', --default:do not send sms @Email_To varchar(1000)='dba@xxx.com', @blocked_duration_ss int=60, --seconds @blocked_count_int int =5 as /********************** {VER EDITOR DATE COMMENT } {ver 1.0 summer.yang 2007-12-13 create procedure } {ver 1.1 summer.yang 2007-12-22 shorten the sms length } {ver 1.2 summer.yang 2007-12-24 to record the dbcc inputbuffer when a spid was killed } {ver 1.3 summer.yang 2008-8-26 add killed running commands } {ver 2.0 summer.yang 2009-3-4 use new sp_block's data } {ver 2.1 summer.yang 2009-3-27 show last sql text } {ver 2.2 summer.yang 2009-4-3 fix bug that sometime email has none text } {ver 3.0 summer.yang 2009-7-20 replace permanent table with executing sp } {ver 3.1 summer.yang 2009-10-28 change table column order,use ##block } REMARK: 1. (default parameter) 监控到某一个进程连续n分钟以上阻塞其他进程,则发送短信报警,并且将此进程取消掉 2. 注意不能kill系统进程 3. 被阻塞的进程超过n个(参数设置) 4. 发送短信调用sp_send_SMS 5. 自动kill spid主要适用于生产主库 6. 每次执行只kill一个进程 7. 不再限制command和status条件 8. 记录被kill的spid的信息(2009-7-20) EXAMPLE: exec dbo.sp_block_AutoKill @SMS_TO ='',--default:do not send sms @Email_To='', @blocked_duration_ss =5, --seconds @blocked_count_int =1 ************************/ set nocount on declare @error int,@rowcount int declare @spid SMALLINT,@duration varchar(55),@hostname sysname,@loginame sysname,@status sysname, @servername sysname,@program_name nvarchar(580),@check_time smalldatetime,@check_time_str char(19) ,@blocked_count int,@dbname sysname,@cmd nvarchar(32),@running_sql nvarchar(max ) ,@running_or_last_text nvarchar(max ) if object_id('master.dbo.dba_spid_activity_log') is null begin --drop table master.dbo.dba_spid_activity_log create table master.dbo.dba_spid_activity_log( id int identity primary key,check_time smalldatetime not null,kill_flag bit --1=killed 0=not kill ,type NVARCHAR(100) not null,spid smallint NULL ,DBname sysname ,login_name sysname ,client_name sysname,client_IP varchar(48),client_program nvarchar(580) ,running_or_last_text nvarchar(max),running_sql nvarchar(max) ,blocking_spid varchar(200)/*varchar(1000)*/,blocked_count varchar(10),blocked_time varchar(50) ,duration varchar(55),session_status nvarchar(60),request_status nvarchar(60) ,command nvarchar(32),wait_type nvarchar(120),wait_resource nvarchar(512) ,open_tran INT ,row_count bigint ,Server_IP varchar(48),Port int ,client_interface NVARCHAR(64),client_process int ,query_plan xml ,login_time datetime,last_request_end_time datetime ,cpu_time int,reads bigint,logical_reads bigint ,Writes bigint,is_user_process bit,blocked_time_ss int ) end --exit when no block exec sp_block '%block%',0 select @error=@@error,@rowcount=COUNT(*) FROM ##block if @rowcount=0 begin print 'There is no blocking and blocked spids.' return 0 END IF @error <>0 BEGIN RAISERROR 55555 'Error when exec sp_block!' RETURN -1 END --exit when light block if not exists( select top 1 * from ##block where blocked_count>=@blocked_count_int and blocked_time_ss>=@blocked_duration_ss ) begin print 'There has block but do not need kill.' return 0 end ------------- select @servername=isnull(stuff(@@servername,charindex('/',@@servername),len(@@servername),''),@@servername) ,@check_time=convert(smalldatetime,getdate()) set @check_time_str=convert(char(19),@check_time,120) declare @spid_kill table(id int,spid int) --如果block spid存在,则记录数据到表 insert into master.dbo.dba_spid_activity_log output inserted.id,inserted.spid into @spid_kill select @check_time,0 ,type ,spid ,DBname ,login_name ,client_name ,client_IP ,client_program ,running_or_last_text ,running_sql ,CONVERT( VARCHAR( 200),blocking_spid) ,blocked_count ,blocked_time ,duration,session_status,request_status ,command,wait_type ,wait_resource ,open_tran ,row_count ,Server_IP,Port ,client_interface ,client_process ,query_plan ,login_time ,last_request_end_time ,cpu_time,reads,logical_reads ,Writes,is_user_process,blocked_time_ss from ##block ------------- select top 1 @hostname=isnull(client_name,''), @dbname=isnull(dbname,''), @loginame=isnull(ltrim(rtrim(login_name)),''), @spid=SPID, @duration=blocked_time, @blocked_count=convert(int,blocked_count), @cmd=isnull(rtrim(command),''), @status=isnull(ltrim(rtrim(session_status)),''), @program_name=convert(nvarchar(14),replace(isnull(client_program,''),' ','')) , @running_sql=isnull(running_sql,'') ,@running_or_last_text= isnull(running_or_last_text,'') from ##block where is_user_process=1 and blocked_count>=@blocked_count_int and blocked_time_ss>=@blocked_duration_ss order by blocked_count desc declare @SMS varchar(180),@sql varchar(8000),@email varchar(8000) declare @Subject varchar(100) set @SMS='' set @email='' --quit if the blocking spid did not fit the conditions if @spid is null begin print 'The spid maybe system process.It will not be killed. ' return end --kill a spid !!! set @sql='kill '+str(@spid) execute(@sql) if @@error<>0 begin set @Subject ='<SYS-ALERT> Kill SPID Failed ('+@servername+'-'+ltrim(str(@spid))+')' set @SMS=substring(@check_time_str,12,8)+' 杀进程'+ltrim(str(@spid))+'(' +@duration+'-'+ltrim(str(@blocked_count))+')失败' raiserror 50000 @SMS end else begin set @Subject ='<SYS-ALERT> Kill SPID OK ('+@servername+'-'+ltrim(str(@spid))+')' set @SMS=substring(@check_time_str,12,8)+' 杀进程'+ltrim(str(@spid))+'(' +@duration+'-'+ltrim(str(@blocked_count))+')成功' --set kill_flag as 1 update master.dbo.dba_spid_activity_log set kill_flag=1 from master.dbo.dba_spid_activity_log a join @spid_kill b on a.id=b.id where b.spid=@spid end --16:12:30 杀进程(00:09:24-10)成功SANDBGAIAAIR.gaiaair_master:GAI declare @msg nvarchar(100) set @msg=@servername+'.'+@loginame+':'+@hostname --cut for sms length if len(@msg)>=30 set @msg=left(@servername,14)+'.'+@loginame+':'+@hostname if len(@msg)>=30 set @msg=left(@servername,14)+'.'+left(@loginame,10)+':'+@hostname if len(@msg)>=30 set @msg=left(@servername,14)+'.'+left(@loginame,10)+':'+left(@hostname,10) set @SMS=@SMS+' '+@msg set @SMS=left(@SMS,66) if len(@SMS_TO )>=11 begin exec dbo.sp_send_SMS @phone=@SMS_TO,/*@phone 请以逗号分隔手机号码*/ @SMS_Msg =@SMS if @@error<>0 begin set @email='Note: Send SMS for auto kill spid failed!' end else begin set @email='Note: Send SMS for auto kill spid OK!' end end else begin set @email='Note: Will not send SMS for auto kill spid!' end -- set @email=@email+char(10) +convert(char(28),'check_time')+convert(char(6),'spid') +convert(char(17),'servername')+convert(char(12),'dbname') +convert(char(17),'loginame') +convert(char(12),'hostname') +convert(char(30),'program_name')+convert(char(16),'cmd') +convert(char(10),'status')--+convert(char(15),'blocked_count') +char(10)--+'<br>' +convert(char(21),@check_time_str)+convert(char(6),ltrim(str(@spid))) +convert(char(17),@servername)+convert(char(12),@dbname) +convert(char(14),@loginame)+convert(char(12),@hostname) +convert(char(30),@program_name)+convert(char(16),@cmd) +convert(char(10),@status)--+convert(char(15),ltrim(str(@blocked_count))) +char(10)--+'<br>' +REPLICATE ('-',80)+char(10) +'==The sending SMS: (length='+ltrim(str(len(@SMS)))+')== '+char(10) +@SMS+char(10) +REPLICATE ('-',80)+char(10) +'==The blocking running SQL== '+char(10) +convert(varchar(4000),@running_sql)+char(10) +REPLICATE ('-',80)+char(10) +'==The blocking Text== '+char(10) +convert(varchar(4000),@running_or_last_text)+char(10) +REPLICATE ('-',80)+char(10) +'==More details== '+char(10) +'select * from master.dbo.dba_spid_activity_log where check_time in (select max(check_time) from master.dbo.dba_spid_activity_log )'+char(10) exec[dbo].[sp_send_mail] @From ='dba@xxx.com' , --发件人 @To =@Email_To,--收件人,split by semicolon, @Subject =@Subject, --标题 @Body =@email --正文 --------------------- print @email GO