背景

如果数据库出现大量阻塞,如不及时进行人工干预,可能会出现数据库hang,严重甚至影响用户业务正常运转。我们希望尽量在出现阻塞的时候及时邮件通知相关干系人去响应,避免出现大量用户报障之后相关处理人员才后知后觉的情况发生,或者有张表可以存储当时的阻塞情况,供我们后期优化分析使用。基于上述需求,我们基于数据库的Job来收集数据库的阻塞情况,并使用sp_澳门凯旋门游戏网址,sent_dbmail发送邮件;

测试环境

Microsoft SQL Server 2012 – 11.0.2100.60 (X64) 

Feb 10 2012 19:39:15 

Copyright (c) Microsoft Corporation

澳门凯旋门注册网址, Enterprise Edition (64-bit) on Windows NT
6.2 <X64> (Build 9200: )

提前准备事项

可以提前注册发送邮件的账号

例如:

邮箱账号:wxchina_monitor@163.com

大家目的在于尽大概在产出堵塞的时候立时邮件公告相关干系人去响应。密码:省略

 

SMTP服务器: smtp.163.com

大家目的在于尽大概在产出堵塞的时候立时邮件公告相关干系人去响应。端口:25

注意登录邮箱设置SMTP

大家目的在于尽大概在产出堵塞的时候立时邮件公告相关干系人去响应。实现流程

大家目的在于尽大概在产出堵塞的时候立时邮件公告相关干系人去响应。大家目的在于尽大概在产出堵塞的时候立时邮件公告相关干系人去响应。 澳门凯旋门注册网址 1

实现步骤

第一步:创建用来存放信息的表

a.新建监控库【azure_monitor】以及存放阻塞记录的表【monitor_blocking】

--创建监控库
IF DB_ID('azure_monitor') IS NULL
    BEGIN
        CREATE DATABASE azure_monitor;
    END;
GO

ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;

 

USE [azure_monitor] --存放阻塞信息的库名
GO
IF OBJECT_ID('monitor_blocking','U') IS NOT NULL
DROP TABLE dbo.Monitor_blocking
GO
CREATE TABLE [dbo].[Monitor_blocking](
[monitor_id] [INT] IDENTITY(1,1) NOT NULL,
[lock type] [NVARCHAR](60) NOT NULL,
[database] [NVARCHAR](128) NULL,
[blk object] [BIGINT] NULL,
[lock req] [NVARCHAR](60) NOT NULL,
[waiter sid] [INT] NOT NULL,
[wait time] [BIGINT] NULL,
[waiter_batch] [NVARCHAR](MAX) NULL,
[waiter_stmt] [NVARCHAR](MAX) NULL,
[blocker sid] [SMALLINT] NULL,
[blocker_stmt] [NVARCHAR](MAX) NULL,
[time] [DATETIME] NOT NULL,
[blocking_date] AS (CONVERT([VARCHAR](100),[time],(23))),
[confirm_flag] [BIT] NULL,
[confirm_user] [NVARCHAR](50) NULL,
[confirm_date] [DATETIME] NULL,
CONSTRAINT [PK_monitor_blocking] PRIMARY KEY CLUSTERED
(
[monitor_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_flag] DEFAULT ((0)) FOR [confirm_flag]
GO
ALTER TABLE [dbo].[Monitor_blocking] ADD CONSTRAINT [DF_Monitor_blocking_confirm_date] DEFAULT (GETDATE()) FOR [confirm_date]
GO

  

第二步:创建收集信息的存储过程

a.创建收集阻塞的存储过程

USE azure_monitor; 
GO 
IF OBJECT_ID('Monitor_p_blocking', 'p') IS NOT NULL
    DROP PROCEDURE Monitor_p_blocking;
GO
----监控库 sql server blocking 的存储过程例子
/*=============================================
-- Author:    jil.wen
-- Create date: 2016/11/16
-- Description:   监控数据库上阻塞情况;
-- demo :   exec dbo.Monitor_p_blocking 
 ============================================= */
CREATE PROCEDURE Monitor_p_blocking
AS
    BEGIN 
        SET NOCOUNT ON;
        INSERT  INTO [dbo].[Monitor_blocking]
                ( [lock type] ,
                  [database] ,
                  [blk object] ,
                  [lock req] ,
                  [waiter sid] ,
                  [wait time] ,
                  waiter_batch ,
                  waiter_stmt ,
                  [blocker sid] ,
                  blocker_stmt ,
                  [time]
                )
                SELECT  t1.resource_type AS [lock type] ,
                        DB_NAME(resource_database_id) AS [database] ,
                        t1.resource_associated_entity_id AS [blk object] ,
                        t1.request_mode AS [lock req]                          -- lock requested    
                        ,
                        t1.request_session_id AS [waiter sid]                      -- spid of waiter    
                        ,
                        t2.wait_duration_ms AS [wait time] ,
                        ( SELECT    text
                          FROM      sys.dm_exec_requests AS r WITH ( NOLOCK ) --- get sql for waiter    
                                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
                          WHERE     r.session_id = t1.request_session_id
                        ) AS waiter_batch ,
                        ( SELECT    SUBSTRING(qt.text,
                                              r.statement_start_offset / 2,
                                              ( CASE WHEN r.statement_end_offset = -1
                                                     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                          * 2
                                                     ELSE r.statement_end_offset
                                                END - r.statement_start_offset )
                                              / 2 + 1)
                          FROM      sys.dm_exec_requests AS r WITH ( NOLOCK )
                                    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
                                    AS qt
                          WHERE     r.session_id = t1.request_session_id
                        ) AS waiter_stmt    --- statement executing now    
                        ,
                        t2.blocking_session_id AS [blocker sid]                --- spid of blocker    
                        ,
                        ( SELECT    text
                          FROM      sys.sysprocesses AS p WITH ( NOLOCK ) --- get sql for blocker    
                                    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
                          WHERE     p.spid = t2.blocking_session_id
                        ) AS blocker_stmt ,
                        GETDATE() time
                FROM    sys.dm_tran_locks AS t1 WITH ( NOLOCK ) ,
                        sys.dm_os_waiting_tasks AS t2 WITH ( NOLOCK )
                WHERE   t1.lock_owner_address = t2.resource_address;
        SET NOCOUNT OFF;
    END; 
 GO

第三步:配置邮件设置,并测试邮件发送是否正常

--配置邮件设置
--1. 启用database mail
use master
GO
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Database mail XPs',1
reconfigure
GO
--2. 添加account
exec msdb..sysmail_add_account_sp
        @account_name            = 'monitor_blocking'                -- mail account
       ,@email_address           = '某某@163.com'       -- sendmail address
       ,@display_name            = 'monitor_blocking'                -- sendusername
       ,@replyto_address         = null
       ,@description             = null
       ,@mailserver_name         = 'smtp.163.com'         -- SMTP Address
       ,@mailserver_type         = 'SMTP'                    -- SMTP
       ,@port                    = 25                        -- port
       ,@username                = '某某@163.com'    -- account
       ,@password                = '替换成密码'    -- pwd
       ,@use_default_credentials = 0
       ,@enable_ssl              = 0                         --is ssl enabled on SMTP server
       ,@account_id              = NULL

--3. 添加profile
exec msdb..sysmail_add_profile_sp
            @profile_name = 'monitor_blocking'         -- profile name
           ,@description  = 'dba mail profile' -- profile description
           ,@profile_id   = NULL

--4. 关联account and profile
exec msdb..sysmail_add_profileaccount_sp 
            @profile_name    = 'monitor_blocking'     -- profile name
           ,@account_name    = 'monitor_blocking'     -- account name
           ,@sequence_number = 1              -- account order in profile 

--5. 测试验证发送database mail
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'monitor_blocking',
        @recipients = '某某@qq.com;某某@163.com;',
        @body = 'This is a testing mail',
        @subject = 'Testing Database Mail';
GO

澳门凯旋门注册网址 2

 后续步骤可以参考数据库出现阻塞及时邮件预警提醒(下)

相关文章