Cannot seem to get Event Notification for blocked processes generate events

Question

I am trying to set up blocked process notification using the script below but I cannot seem to get event's generated, I confirmed blocked process threshold is set to 25 and database enabled for service broker and also is trustworthy property of the database is on. I first ran the below script.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold', 25
GO
RECONFIGURE
GO
EXEC sp_configure  'blocked process threshold'
GO
USE DBA
GO
IF NOT EXISTS ( SELECT  *
                FROM    sys.databases(NOLOCK) WHERE   name = 'DBA' AND is_broker_enabled = 1 ) 
    BEGIN
        ALTER DATABASE Util SET NEW_BROKER WITH ROLLBACK IMMEDIATE
    END
GO
DROP SERVICE [BlocksService]
GO
DROP QUEUE [BlocksQueue]
GO
DROP EVENT NOTIFICATION [CaptureBlocks]  ON SERVER
GO
--  Create a service broker queue to hold the events
CREATE QUEUE [BlocksQueue]
GO
--  Create a service broker service receive the events
CREATE SERVICE [BlocksService]
ON QUEUE [BlocksQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
CREATE EVENT NOTIFICATION [CaptureBlocks]
ON SERVER
WITH FAN_IN
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlocksService', 'current database';
GO

And from connection B I ran this script

BEGIN TRANSACTION
CREATE TABLE TEST(ID INT)

and from connection C I ran this script

SELECT * FROM TEST

and from connection D I am checking if any events are in the que, but even after minutes of waiting nothing showed up.

SELECT * FROM [BlocksQueue]

Is there anything I am doing wrong or how can I troubleshoot this issue and get events notified, do I have to start sql server with a certain trace option on?

Thank you

---

Gokhan Varol

Answer 1

Hi Gokhan,

Try to remove

“IF NOT EXISTS ( SELECT  *
                FROM    sys.databases(NOLOCK) WHERE   name = 'DBA' AND is_broker_enabled = 1 )
    BEGIN
        ALTER DATABASE Util SET NEW_BROKER WITH ROLLBACK IMMEDIATE
END”

from your query and you can also use “SELECT cast( message_body as xml ), * FROM [BlocksQueue]” instead of “SELECT * FROM [BlocksQueue]”


Please see this blog which describe how to monitoring blocked processes use event notification:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Iric

I already followed the link you provided when I wrote the piece of code. My issue is the event does not get fired does not matter how many times I reenable service broker or set new broker. I would like to find out how to troubleshoot and solve this issue.

Any advice?

thank you

---

Gokhan Varol

Answer 3

Hi Gokhan,

Is it possible that you are creating the blocking condition in another database besides where the queue and service is defined. In another database besides DBA? If so then the event is being triggered but it cant find the service 'BlockerService' to sent the message to because it is not in the current database. Instead of 'current database' you can use the service_broker_guid for the DBA database.

CREATE EVENT NOTIFICATION [CaptureBlocks]
ON SERVER
WITH FAN_IN
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlocksService', 'current database';
GO

You can get the service_broker_guid from sys.databases and replace 'current database' with it. For example.

select name, service_broker_guid from sys.databases where name = 'DBA'

CREATE EVENT NOTIFICATION [CaptureBlocks]
ON SERVER
WITH FAN_IN
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlocksService','F49D743A-BAA8-44C4-B4DA-5329CE57BD93';
GO

Other useful views:

select * from sys.server_event_notifications      -- server specific

select * from sys.event_notifications      -- database specific

HTH,

Bill -- Microsoft CTS