How to configure Block Process Report

Jul 11 at 5:31 AM
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)
GO

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?

Thanks,
Sri
Coordinator
Jul 11 at 2:45 PM
Hey Sri,

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.

Jonathan Kehayias
Principal Consultant, SQLskills.com
SQL Server MVP, Microsoft Certified Master: SQL Server 2008