conn=ISNULL(cntr_value,0) fromsys.dm_os_performance_counters with(nolock)
wherecounter_name='User Connections'
if@conn>=550--根据用户链接数来收集信息和触发报警
begin
--active requests
Select@activeconn=isnull(SUM(cntr_value),0) fromsys.dm_os_performance_counters with(nolock)
wherecounter_name ='Active requests'
groupbycounter_name
set@count=1
if@activeconn>=40 --根据活动链接数来判断
begin
while1=1 --循环收集信息
begin
execRecodeAndAlertInfo
if(@count%5=0) --连续次就发邮件
begin
set@subject='Server:'+@@SERVERNAME+' Connections Alert'
SET@body='Server:'+@@SERVERNAME+CHAR(13)+'; UserConnections:'+cast(@connasvarchar)+CHAR(13)+'; ActiveRequests:'+cast(@activeconnasvarchar)
EXECmsdb.dbo.sp_send_dbmail
@recipients=N'Ken@xxx.com;itmon@xxx.com',
@body=@body,
@body_format='TEXT',
@subject=@subject,
@profile_name='dba_profile'--需要配置该模板
end
Select@activeconn=isnull(SUM(cntr_value),0) fromsys.dm_os_performance_counters with(nolock)
wherecounter_name ='Active requests'
groupbycounter_name
if@activeconn>=40
waitfordelay '00:00:05'
else
break;
if@count>50
break;
set@count=@count+1;
end
end
end
else--根据Blocked 百分比来收集信息和触发报警
begin
select@spidcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50
select@blockedcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50andblocked<>0
--计算百分比
set@spidblocked=@blockedcount/@spidcount
set@count=1
if(@spidblocked>=0.1) --10%时，记录信息
begin
while1=1 --循环收集信息
begin
execRecodeAndAlertInfo
if(@count%5=0or@spidblocked>=0.15) --连续5次阻塞比率大于10%或者阻塞比率大于等于15%时，记录信息并发生报警邮件
begin
set@subject='Server:'+@@SERVERNAME+' Blocked Alert'
SET@body='Server:'+@@SERVERNAME+CHAR(13)+'; BlockedCount:'+cast(@blockedcountasvarchar)+CHAR(13)+'; SPIDCount:'+cast(@spidcountasvarchar)
EXECmsdb.dbo.sp_send_dbmail
@recipients=N'Ken@xxx.com',
@body=@body,
@body_format='TEXT',
@subject=@subject,
@profile_name='dba_profile'
end
select@spidcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50
select@blockedcount=count(0) fromsys.sysprocesses with(nolock) wherespid>50andblocked<>0
set@spidblocked=@blockedcount/@spidcount
if@spidblocked>=0.1
waitfordelay '00:00:05'
else
break;
if@count>50
break;
set@count=@count+1;
end
end
end
end
4. 查看信息：
USE[m |