Automating Alerting on Blocking with Queues and Event Notifications

0 votes
0 answers
87 views
sql-server blocking event-notification queue
                          I need to automate email notifications on blocking on some SQL 2016 servers. I have had a look at running a job through the SQL agent but seems hack n slash. I found an alternative method online that uses queues and event notifications (can't find source again) that seems more suited to the task, however the queue is not populating with entries and am not sure why.

Below is the code I found but it is not working:

    USE msdb

    GO
    
    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    EXEC sp_configure 'blocked process threshold', 20
    RECONFIGURE
    GO
    
    IF exists (select * from sys.services where name like 'BlockedProcessService')
        DROP SERVICE [BlockedProcessService]
    GO
    
    IF exists (select * from sys.service_queues where name like 'BlockedProcessQueue')
        DROP QUEUE [dbo].BlockedProcessQueue
    GO
    
    CREATE QUEUE BlockedProcessQueue
    
    CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) 
    GO
    
    IF exists (select * from sys.objects where name = 'wait_resource_name' and type = 'TF')
    	DROP FUNCTION [dbo].[wait_resource_name]
    GO
    
    CREATE FUNCTION [dbo].[wait_resource_name](@obj nvarchar(max))
    RETURNS @wait_resource TABLE (
        wait_resource_database_name sysname,
        wait_resource_schema_name sysname,
        wait_resource_object_name sysname
    )
    AS
    BEGIN
        DECLARE @dbid int
        DECLARE @objid int
    
        IF @obj IS NULL RETURN
        IF @obj NOT LIKE 'OBJECT: %' RETURN
    
        SET @obj = SUBSTRING(@obj, 9, LEN(@obj) - 9 + CHARINDEX(':', @obj, 9))
    
        SET @dbid = LEFT(@obj, CHARINDEX(':', @obj, 1) - 1)
        SET @objid = SUBSTRING(@obj, CHARINDEX(':', @obj, 1) + 1, CHARINDEX(':', @obj, CHARINDEX(':', @obj, 1) + 1) - CHARINDEX(':', @obj, 1) - 1)
    
        INSERT INTO @wait_resource (wait_resource_database_name, wait_resource_schema_name, wait_resource_object_name)
        SELECT db_name(@dbid), object_schema_name(@objid, @dbid), object_name(@objid, @dbid)
    
        RETURN
    END
    GO
    
    
    use master
    go
    
    IF exists (select * from sys.objects where name = 'StartBlockedProcessNotification' and type = 'P')
    	DROP PROCEDURE [dbo].StartBlockedProcessNotification
    GO
    
    CREATE PROCEDURE StartBlockedProcessNotification
    AS
    begin
    
    	if exists (select * from sys.server_event_notifications where name = 'BlockedProcessNotification')
    	begin
    		drop event notification BlockedProcessNotification on server
    	end
    
    	CREATE EVENT NOTIFICATION BlockedProcessNotification ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BlockedProcessService', 'msdb'
    end
    GO
    
    EXEC sp_procoption 'StartBlockedProcessNotification', 'startup', 'on'
    
    EXEC StartBlockedProcessNotification
    
    use msdb
    go
    
    if exists (select * from sys.objects where name = 'BlockedProcessActivationProcedure' and type = 'P')
    	drop procedure BlockedProcessActivationProcedure
    go
    
    
    CREATE PROCEDURE BlockedProcessActivationProcedure
    AS
    
    --Service Broker
    DECLARE @message_body xml
    DECLARE @message_body_text nvarchar(max)
    DECLARE @dialog uniqueidentifier
    DECLARE @message_type nvarchar(256)
    
    WHILE 1 = 1
    BEGIN --Process the queue
        BEGIN TRANSACTION;
    
        RECEIVE TOP (1)
            @message_body = message_body,
            @dialog = conversation_handle,
            @message_type = message_type_name
        FROM BlockedProcessQueue
    
        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('Nothing more to process', 0, 1)
            ROLLBACK TRANSACTION
            RETURN
        END
    
        IF @message_type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification '
        BEGIN
            DECLARE @mail_body nvarchar(max)
    
            DECLARE @post_time varchar(32)
            DECLARE @duration int
            DECLARE @blocked_spid int
            DECLARE @waitresource nvarchar(max)
            DECLARE @waitresource_db nvarchar(128)
            DECLARE @waitresource_schema nvarchar(128)
            DECLARE @waitresource_name nvarchar(128)
            DECLARE @blocked_hostname nvarchar(128)
            DECLARE @blocked_db nvarchar(128)
            DECLARE @blocked_login nvarchar(128)
            DECLARE @blocked_lasttranstarted nvarchar(32)
            DECLARE @blocked_inputbuf nvarchar(max)
            DECLARE @blocking_spid int
            DECLARE @blocking_hostname nvarchar(128)
            DECLARE @blocking_db nvarchar(128)
            DECLARE @blocking_login nvarchar(128)
            DECLARE @blocking_lasttranstarted nvarchar(32)
            DECLARE @blocking_inputbuf nvarchar(max)
    
    		declare @notificationSubject varchar(125)
    
            SET @post_time = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/PostTime)', 'datetime'), 109)
            SET @duration = CAST(@message_body.value(N'(//EVENT_INSTANCE/Duration)', 'bigint') / 1000000 AS int)
            SET @blocked_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)', 'int')
            SET @waitresource = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@waitresource)', 'nvarchar(max)')
            SET @blocked_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)', 'nvarchar(128)')
            SET @blocked_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@currentdb)', 'int'))
            SET @blocked_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)', 'nvarchar(128)')
            SET @blocked_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@lasttranstarted)', 'datetime'), 109)
            SET @blocked_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/inputbuf)', 'nvarchar(max)')
            SET @blocking_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)', 'int')
            SET @blocking_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)', 'nvarchar(128)')
            SET @blocking_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@currentdb)', 'int'))
            SET @blocking_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)', 'nvarchar(128)')
            SET @blocking_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@lasttranstarted)', 'datetime'), 109)
            SET @blocking_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf)', 'nvarchar(max)')
    		
    		set @notificationSubject = 'MS SQL Server - Blocked Process Report: ' + @@servername
    
            SELECT
                @waitresource_name = wait_resource_object_name,
                @waitresource_schema = wait_resource_schema_name,
                @waitresource_db = wait_resource_database_name
            FROM master.dbo.wait_resource_name(@waitresource)
    
            SET @mail_body = 'Posted: ' + ISNULL(@post_time, '') + CHAR(10) +
                'Duration: ' + ISNULL(CAST(@duration AS varchar) + ' s', '') + CHAR(10) +
                CHAR(10) +
                '==========Blocked Process==========' + CHAR(10) +
                'SPID: ' + ISNULL(CAST(@blocked_spid AS varchar), '') + CHAR(10) +
                'Wait Resource: ' + ISNULL(ISNULL(QUOTENAME(@waitresource_db) + '.' + QUOTENAME(@waitresource_schema) + '.' + QUOTENAME(@waitresource_name), @waitresource), '') + CHAR(10) +
                'Hostname: ' + ISNULL(@blocked_hostname, '') + CHAR(10) +
                'Current Database: ' + ISNULL(@blocked_db, '') + CHAR(10) +
                'Login Name: ' + ISNULL(@blocked_login, '') + CHAR(10) +
                'Last Transaction Started: ' + ISNULL(@blocked_lasttranstarted, '') + CHAR(10) +
                '----------Input Buffer----------' + CHAR(10) +
                ISNULL(@blocked_inputbuf, '') + CHAR(10) +
                CHAR(10) +
                '==========Blocking Process==========' + CHAR(10) +
                'SPID: ' + ISNULL(CAST(@blocking_spid AS varchar), '') + CHAR(10) +
                'Hostname: ' + ISNULL(@blocking_hostname, '') + CHAR(10) +
                'Current Database: ' + ISNULL(@blocking_db, '') + CHAR(10) +
                'Login Name: ' + ISNULL(@blocking_login, '') + CHAR(10) +
                'Last Transaction Started: ' + ISNULL(@blocking_lasttranstarted, '') + CHAR(10) +
                '----------Input Buffer----------' + CHAR(10) +
                ISNULL(@blocking_inputbuf, '') + CHAR(10)
    
            EXEC sp_send_dbmail @profile_name = 'MS SQL Server Alerts',  @recipients = 'Mssqlserveralerts ', @subject = @notificationSubject, @body = @mail_body
        END
        ELSE IF @message_type IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error ', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ')
        BEGIN
    
    	EXEC sp_send_dbmail @profile_name = 'MS SQL Server Alerts',  @recipients = '', @subject = @notificationSubject, @body = @dialog
    
            END CONVERSATION @dialog
        END
    
        COMMIT TRANSACTION
    END
    
    GO
    
    ALTER QUEUE BlockedProcessQueue WITH ACTIVATION (
        STATUS = ON,
        PROCEDURE_NAME = [BlockedProcessActivationProcedure],
        MAX_QUEUE_READERS = 1,
        EXECUTE AS OWNER
    )


### Questions

1. What is the cause queue not being populated? 
2. Is there better method of altering on blocking than with queues and event notifications?

-----
_**Reopen Request**_  
_While the Question and Answer that are linked in the **duplicate** close reason probably do answer the second question, they do not answer the first question: **What is the cause of the above alerting not working?**_.
                        
Asked by Luke (129 rep)
Jun 20, 2018, 11:37 AM
Last activity: Jun 22, 2018, 10:42 AM
Automating Alerting on Blocking with Queues and Event Notifications

Related Questions
