sqlserver 通过自己编写sql作业来获得通知_individual query sqlserver-CSDN博客

本文链接：https://blog.csdn.net/u011334954/article/details/95347513
根据进程堵塞时间长度自己编写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;
配置自行更改