USE azure_monitor;
GO
IF OBJECT_ID('monitor_p_sent_mail', 'P') IS NOT NULL
DROP PROCEDURE monitor_p_sent_mail;
GO
----监控库 sql server blocking 的存储过程例子
/*=============================================
-- Author: jil.wen
-- Create date: 2016/11/16
-- Description: 新建存储过程发送关于阻塞的邮件;
-- demo : exec dbo.monitor_p_sent_mail
============================================= */
CREATE PROCEDURE monitor_p_sent_mail
AS
BEGIN
--注意替换库名的修改
SET NOCOUNT ON;
IF EXISTS ( SELECT 1
FROM azure_monitor..Monitor_blocking
WHERE blocking_date = ( CONVERT([VARCHAR](100), GETDATE(), ( 23 )) )
AND confirm_flag = 0
AND [database] = N'替换成实际被监控的库名' ) --替换成实际的库名
BEGIN
DECLARE @getdate VARCHAR(200);
DECLARE @xiangmu VARCHAR(200);
SELECT @xiangmu='某某项目名称' --替换成实际的项目名称
SELECT @getdate =@xiangmu + '【' + CONVERT(VARCHAR(12), GETDATE(), 112)
+ ' 出现阻塞预警】';
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'monitor_blocking',
@recipients = '某某@qq.com;', ---收件人,多个邮箱用【;】区分
@copy_recipients = 'someone@163.com;', --抄送人
@query = 'SELECT [lock type], [database], [blk object], [lock req], [waiter sid], [wait time], waiter_batch, waiter_stmt, [blocker sid], blocker_stmt, time, blocking_date
FROM azure_monitor..monitor_blocking WHERE blocking_date=(CONVERT([varchar](100),GETDATE(),(23))) and confirm_flag = 0 and [database]=N''替换成实际被监控的库名''',
@subject = @getdate,
@body = '各位好,
数据库已出现阻塞了,如需查看阻塞语句请使用下述脚本:
SELECT [lock type] ,
[database] ,
[blk object] ,
[lock req] ,
[waiter sid] ,
[wait time] ,
waiter_batch ,
waiter_stmt ,
[blocker sid] ,
blocker_stmt ,
time ,
blocking_date
FROM azure_monitor..monitor_blocking
WHERE blocking_date = ( CONVERT([varchar](100), GETDATE(), ( 23 )) ) and confirm_flag = 0 and [database]=N''替换成实际被监控的库名'' --请替换成实际的库名
请及时响应并更改已处理阻塞的处理状态,不然会一直发送预警邮件,建议处理阻塞状态脚本如下语句:
例如:
UPDATE azure_monitor..Monitor_blocking
SET confirm_flag = 1 , --处理标志
confirm_user = N''张三'' , --更新人
confirm_date = GETDATE() --处理时间
WHERE blocking_date = ( CONVERT([VARCHAR](100), GETDATE(), ( 23 )) ) --阻塞日期
AND [database] = N''替换成实际被监控的库名'' --替换成实际的库名
AND [time] < DATEADD(MINUTE, -15, GETDATE()); ---当前时间向前推15分钟 ',
@attach_query_result_as_file = 1;
END;
SET NOCOUNT OFF;
END;
USE azure_monitor;
GO
IF OBJECT_ID('monitor_p_sent_mail', 'P') IS NOT NULL
DROP PROCEDURE monitor_p_sent_mail;
GO
----监控库 sql server blocking 的存储过程例子
/*=============================================
-- Author: jil.wen
-- Create date: 2016/11/16
-- Description: 新建存储过程发送关于阻塞的邮件;
-- demo : exec dbo.monitor_p_sent_mail
============================================= */
CREATE PROCEDURE [dbo].[monitor_p_sent_mail]
AS
BEGIN
--注意替换库名的修改
SET NOCOUNT ON;
IF EXISTS ( SELECT 1
FROM azure_monitor..Monitor_blocking
WHERE blocking_date = ( CONVERT([VARCHAR](100), GETDATE(), ( 23 )) )
AND confirm_flag = 0
AND [database] = N'替换成实际库名' ) --替换成被监控的实际库名
BEGIN
DECLARE @xml NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);
SET @xml = CAST(( SELECT [lock type] AS 'td' ,
'' ,
[database] AS 'td' ,
'' ,
[blk object] AS 'td' ,
'' ,
[lock req] AS 'td' ,
'' ,
[waiter sid] AS 'td' ,
'' ,
[wait time] AS 'td' ,
'' ,
waiter_