SQL数据库有阻塞就自动发邮件警报

view [dbo].[VW_WaitingCount] as SELECT s.session_id, r.blocking_session_id, s.host_name, s.login_name, databasename = db_name(r.database_id), r.command, r.status AS sqlexecstatus, current_execute_sql = substring(t .text, r.statement_start_offset / 2 + 1, CASE WHEN statement_end_offset = - 1 THEN len(t .text) ELSE (r.statement_end_offset - statement_start_offset) / 2 + 1 END), s.program_name, s.status, s.cpu_time, memory_usage_kb = s.memory_usage * 8, s.reads, s.writes, s.transaction_isolation_level, c.connect_time, c.last_read, c.last_write, c.net_transport, c.client_net_address, c.client_tcp_port, c.local_tcp_port, r.start_time, r.wait_time, r.wait_type, r.last_wait_type, r.wait_resource, r.open_transaction_count, r.transaction_id FROM sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id AND c.connection_id = r.connection_id OUTER apply sys.dm_exec_sql_text(r.sql_handle) t WHERE 1 = 1 AND s.is_user_process = 1 AND command IS NOT NULL

2.配置SQL发送邮件（略去。。。）

3.建存储过程

USE [qhw_shop]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CheckBlockingAndSendmail]
@sendtype int =1
as
declare @ccount int
select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
--print @ccount
if(@ccount>0)
begin
    waitfor delay ‘00:00:10‘--定义等待10秒 
    select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
    if(@ccount>0)
        begin
            waitfor delay ‘00:00:10‘--定义等待10秒 
            select @ccount=COUNT(1) from [VW_WaitingCount] where blocking_session_id<>0
            if(@ccount>0)
            begin
                if @sendtype=1
                    begin
                        --发送简单文本的邮件
                        EXEC msdb..sp_send_dbmail
                        @profile_name = ‘sendmailby126‘,--profile名称
                        @recipients = ‘46161681@qq.com;davidhou@126.com‘,--收件人
                        @subject = N‘数据库有阻塞，请登录服务器查看‘,--邮件标题
                        @body = N‘数据库有阻塞，请登录服务器查看‘,--邮件内容
                        @body_format = ‘HTML‘--邮件格式
                    end 
                if @sendtype=2
                    begin
                        
                        --发送包含查询的邮件
                        EXEC msdb..sp_send_dbmail
                        @profile_name = ‘sendmailby126‘,
                        @recipients = ‘46161681@qq.com;davidhou@126.com‘,
                        @subject = ‘数据库有阻塞，结果见正文‘,
                        --@query = ‘SET NOCOUNT  ON;select getdate()‘
                        --@query = ‘select getdate() as 发生时间;select count(1) 记录总数 from [VW_WaitingCount] where blocking_session_id<>0;select * from vw_waitingcount where session_id in (select blocking_session_id from vw_waitingcount where blocking_session_id>0) and blocking_session_id=0‘
                        @query = ‘select getdate() as 发生时间;select session_id,blocking_session_id,databasename,current_execute_sql,wait_time,last_wait_type from [VW_WaitingCount] where session_id in (select blocking_session_id from [qhw_shop].[dbo].[VW_WaitingCount] where blocking_session_id>0) and blocking_session_id=0‘
                        --,@body_format = ‘HTML‘--邮件格式
                    end
            end
        end 
end

4.建立作业，每隔1分钟或10分钟或多长时间自己设置，作业执行如下代码

exec dbname.dbo.CheckBlockingAndSendmail @sendtype=2

5.邮箱可以和微信绑定，一有阻塞警报，微信即可收到信息，登录服务器，查询出阻塞的源头SPID,并查询出执行的sql语句，如果不是很重要，可以KILL掉。

 

SQL数据库有阻塞就自动发邮件警报

标签：