I have downloaded SSMS Addin for extended events. I would like to configure Block Process Report on a server and receive notification every time if blocking happens.
I created the same process in SQL Server 2012 and trying to deploy in SQL 2008 R2 64 Bit server environment. I get the error on the first place to create event session. Here is the error message
CREATE EVENT SESSION [MonitorBlocking] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
Here is the Error:
Msg 25623, Level 16, State 1, Line 1
The event name, "sqlserver.blocked_process_report", is invalid, or the object could not be found
Any help is greatly appreciated in this regard.
We have company wide policy not to go with Service Broker - Event notification route.
I need to configure Extended events and capture the block_process_report events whenever blocking occurs.
Is this doable?
Jul 11 at 2:45 PM
No it is not possible in 2008 with Extended Events because the blocked_process_report event does not exist there. You would have to collect the reports using SQL Trace in 2008R2 and earlier.