Event Notifications Blocked Process SQL Server: Set Up

SQL Server’s blocked process report is an XML event fired when a lock wait exceeds the blocked process threshold. Set sp_configure 'blocked process threshold' in seconds, create a Service Broker queue and an Event Notification for BLOCKED_PROCESS_REPORT, then read the XML to identify the blocking and blocked sessions.

This guide captures blocked process reports with Event Notifications on SQL Server 2016 or later, so you can identify blocking and blocked sessions without a profiler trace. You need ALTER ANY EVENT NOTIFICATION server permission and a database enabled for Service Broker. The steps were tested on SQL Server 2019 CU18; behaviour differs before 2016 SP1.

What SQL Server’s blocked process report is

SQL Server fires the BLOCKED_PROCESS_REPORT event when a lock wait exceeds the blocked process threshold, measured in seconds. The threshold is set with sp_configure 'blocked process threshold', N; a value of 0 disables the report. The event arrives as an XML document through Event Notifications. It sits alongside Extended Events: Event Notifications cover server-level events, while an XE session is often used for trace-like collection. The XML report identifies the blocked session, the blocking session, and the resource they wait on. Use Event Notifications when you want a message-based capture without a continuous trace or an XE session.

Enable the blocked process threshold

Run sp_configure to show advanced options, then set the threshold. The value is in seconds and applies to the whole instance.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 20;
RECONFIGURE;

The RECONFIGURE calls apply the settings immediately. The threshold in this example is 20 seconds; choose a value that avoids noise but catches waits that matter. Verify with sp_configure 'blocked process threshold'; both config_value and run_value should show 20 after reconfiguration. Set the value back to 0 to disable the report. The threshold is a server-level setting, so it affects blocked process reports for all databases on the instance.

Create the Service Broker queue and service

Use a dedicated queue so blocked process events do not mix with other Service Broker traffic. The queue holds messages until you receive them; activation is not required when you consume with RECEIVE. The service must advertise the contract that Event Notifications uses, or delivery will fail.

CREATE QUEUE BlockedProcessQueue;

CREATE SERVICE BlockedProcessService
ON QUEUE BlockedProcessQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

A contract defines the message types a service can send and receive. If the service does not list the correct contract, delivery fails. Use unique queue and service names in a shared environment. Service Broker delivery is transactional; if the database is offline or broker is disabled, messages wait in sys.transmission_queue instead of reaching the queue.

Create the event notification for BLOCKED_PROCESS_REPORT

Create the notification at server scope so it fires for blocked process reports from any database. The event name in T-SQL is BLOCKED_PROCESS_REPORT, not BLOCKED_PROCESS.

CREATE EVENT NOTIFICATION BlockedProcessNotification
ON SERVER
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlockedProcessService', 'current database';

The ON SERVER clause is mandatory for this event. Messages go to the queue backing BlockedProcessService in the current database; the queue retains them until you run RECEIVE. Confirm the object exists with sys.server_event_notifications.

SELECT name, service_name
FROM sys.server_event_notifications
WHERE name = N'BlockedProcessNotification';

The service_name returned by the view must match the name used in TO SERVICE. 'current database' resolves the broker instance of the current database. The notification is server-scoped, so check sys.server_event_notifications rather than the database-level sys.event_notifications view.

Read the blocked process report XML

The XML payload begins at blocked-process-report and contains one blocked-process element and at least one blocking-process element. Each process element carries id, spid, waitresource, and optionally cpu; inputbuf is a child of process and holds the last statement submitted by the session.

<blocked-process-report monitorLoop="1044">
  <blocked-process>
    <process id="process4b8c1c8" spid="52" waitresource="KEY: 5:72057594044284928 (1a39e609515c)" cpu="1400" status="suspended" lockMode="U">
      <inputbuf>UPDATE dbo.Orders SET Status = 'Packed' WHERE OrderId = 1001;</inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process id="process4b8c1c9" spid="51" waitresource="KEY: 5:72057594044284928 (1a39e609515c)" cpu="0" status="sleeping" lockMode="X">
      <inputbuf>BEGIN TRANSACTION; UPDATE dbo.Orders SET Status = 'Held' WHERE OrderId = 1001;</inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

In this example, session 52 waits on the key for dbo.Orders.OrderId = 1001, and session 51 holds the incompatible lock. Both sessions show the same waitresource, so the block is on one index key.

Element	What it shows
`blocked-process-report`	Root of the report; `monitorLoop` is the monitoring cycle identifier.
`blocked-process`	Session that is waiting.
`blocking-process`	Session holding the lock.
`process`	Session details: `id`, `spid`, `waitresource`, and optionally `cpu`, `status`, and `lockMode`.
`waitresource`	Resource identifier that shows the object or key being waited on.
`cpu`	CPU time attribute when present in the report.
`inputbuf`	Child of `process`; the last input buffer for a session, useful for the query text.

Open the message_body from the queue to see the full report for your incident.

Verify the notification and troubleshoot common failures

To test, open two sessions. In session A, begin a transaction and update a row; in session B, run a statement that waits on the same row. After the threshold has passed, receive the report from the queue.

RECEIVE TOP (1)
    message_type_name,
    CAST(message_body AS XML) AS blocked_report
FROM BlockedProcessQueue;

Check sys.service_queues and sys.transmission_queue if no message arrives. Common failures are Service Broker disabled in the database, a missing service contract, or missing ALTER ANY EVENT NOTIFICATION permission.

FAQ

Where is the documentation for SQL Server blocked process report?

There is no single consolidated page. Reconstruct it from Microsoft Learn articles on the threshold, Event Notifications, and the event class.

How do I enable blocked process threshold?

Use sp_configure 'blocked process threshold', N in seconds and RECONFIGURE; 0 disables it.

Why is my blocked process alert showing no object or empty process name?

Application Name can be empty in the connection string, and the XML does not guarantee an object name.

Does Event Notifications work on SQL Server 2016, 2019 and 2022?

Yes. The pattern is the same, but the threshold and Service Broker require the listed permissions.

How do I read the blocked process report XML?

Start with blocking-process and blocked-process, then waitresource, cpu, and inputbuf.

Sources

Microsoft Learn, “blocked process threshold Server Configuration Option” — https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option
Microsoft Learn, “Event Notifications” — https://learn.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications
Microsoft Learn, “CREATE EVENT NOTIFICATION (Transact-SQL)” — https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql

Event Notifications Blocked Process SQL Server: End-to-End Setup