sqlserver 通过自己编写sql作业来获得通知

以你之姓@ 2021-12-11 06:29 200阅读 0赞

根据进程堵塞时间长度自己编写sql,通过调度作业发送邮件给DBA，不通过sqlserver警报，灵活性高

SET NOCOUNT ON;
    DECLARE @dt DATETIME= GETDATE();
     -- 阻塞时间
    DECLARE @HtmlContent NVARCHAR(MAX);
    
    DECLARE @waittime [bigint];
    
     --邮件发送的阻塞日志（表格形式）
    
    IF OBJECT_ID('tempdb.dbo.#BlockLog') IS NOT NULL 
        DROP TABLE #BlockLog;     
    --将当前日志记录插入临时表
    BEGIN  
        WITH sess AS
    (
        SELECT         DATEDIFF(S,er.start_time,GETDATE()) as duration,
            es.session_id,
            database_name = DB_NAME(er.database_id),
            er.cpu_time,
            er.reads,
            er.writes,
            er.logical_reads,
            login_name,
    
            er.status,
            blocking_session_id,
            wait_type,
            wait_resource,
            wait_time,
            individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
            parent_query = qt.text,
            program_name,
            host_name,
            nt_domain,
            start_time,
           @dt dt
        FROM
            sys.dm_exec_requests er
            INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
        WHERE
            es.session_id > 50
            AND es.session_Id NOT IN (@@SPID)
    		AND qt.text <> 'sp_server_diagnostics'
    )
    SELECT
        *
    	INTO #BlockLog
    FROM
        sess WHERE individual_query NOT LIKE 'waitfor%'
    UNION ALL SELECT  DATEDIFF(S,es.last_request_start_time,GETDATE()) as duration,
        es.session_id,
        database_name = '',
        0,
        0,
        0,
        0,
        login_name,
        es.status,
        0,
        '',
        '',
        '',
        qt.text,
        parent_query = qt.text,
        program_name,
        host_name,
        nt_domain,
        es.last_request_start_time,
    	@dt dt
    FROM
        sys.dm_exec_sessions es
        INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
        CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
    WHERE
        ec.most_recent_session_id IN
        (
            SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
        ) AND  qt.text NOT in ('waitfor delay @waittime    ','sp_server_diagnostics')
    	
    ORDER BY 1, 2
    
    SELECT @waittime=MAX(duration) FROM #BlockLog;
    --SELECT MAX(duration) FROM #BlockLog;
    
    IF  @waittime >180
    begin
    --将临时表数据插入日志表
        INSERT  INTO  test.dbo.BlockLog
                (  duration ,
           session_id ,
            database_name ,
            cpu_time ,
            reads ,
            writes ,
            logical_reads ,
            login_name ,
            status ,
            blocking_session_id ,
            wait_type ,
            wait_resource ,
            wait_time ,
            individual_query ,
            parent_query  ,
            program_name ,
            host_name ,
            nt_domain ,
            start_time ,
    		dt
                )
                SELECT   duration,
           session_id,
            database_name,
            cpu_time,
            reads,
            writes,
            logical_reads,
            login_name,
            status,
            blocking_session_id,
            wait_type,
            wait_resource,
            wait_time,
            individual_query ,
            parent_query ,
            program_name,
            host_name,
            nt_domain,
            start_time,
    		dt
                FROM    #BlockLog;
    --以html表格方式发送邮件，如果不发送邮件，则删除以下代码
        SET @HtmlContent = N'<head>'
            + N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>'
            + N'<table border="1">' + N'<tr>
                             <th>duration </th>
            <th>session_id </th>
            <th>database_name </th>
            <th>cpu_time </th>
            <th>reads </th>
            <th>writes </th>
            <th>logical_reads </th>
            <th>login_name </th>
            <th>status </th>
            <th>blocking_session_id </th>
            <th>wait_type </th>
            <th>wait_resource </th>
            <th>wait_time </th>
            <th>individual_query </th>
            <th>parent_query  </th>
            <th>program_name </th>
            <th>host_name </th>
            <th>nt_domain </th>
            <th>start_time </th>
    		<th>dt</th>
                    </tr>' + CAST(( SELECT          duration as TD,
    		'' ,
            session_id as TD,
    		'' ,
            database_name as TD,
    		'' ,
            cpu_time as TD,
    		'' ,
            reads as TD,
    		'' ,
            writes as TD,
    		'' ,
            logical_reads as TD,
    		'' ,
            login_name as TD,
    		'' ,
            status as TD,
    		'' ,
            blocking_session_id as TD,
    		'' ,
            wait_type as TD,
    		'' ,
            wait_resource as TD,
    		'' ,
            wait_time as TD,
    		'' ,
            individual_query as TD,
    		'' ,
            parent_query  as TD,
    		'' ,
            program_name as TD,
    		'' ,
            host_name as TD,
    		'' ,
            nt_domain as TD,
    		'' ,
            start_time as TD,
    		'' ,
    		dt as TD,
    		'' 
                                    FROM    #BlockLog
                                  FOR
                                    XML PATH('tr') ,
                                        TYPE
                                  ) AS NVARCHAR(MAX)) + N'</table>';
        IF @HtmlContent IS NOT NULL 
            BEGIN
                DECLARE @ProfileName VARCHAR(100)= 'test'; --邮箱公用账户名称
                DECLARE @RecipientsLst VARCHAR(100)= '1111111@qq.com'  --收件人，以";"分隔
                DECLARE @subject VARCHAR(100)= '数据库阻塞警报'; --主题
                EXEC msdb.dbo.sp_send_dbmail @profile_name = @ProfileName,
                    @recipients = @RecipientsLst, @subject = @subject,
                    @body = @HtmlContent, @body_format = 'HTML'; 
            END; 
    		END;  
        begin  
            DROP TABLE #BlockLog;  
        END;                              
    END;

配置自行更改