Pentru a primi o notificare cand evenimentul are loc, folositi functionalitatea blocked process reports:
1. Setati o valoare pentru blocked process threshold:

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
-- use 20 seconds in this case
EXEC sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;
GO

2. Folositi SQL Server Agent pentru a defini o alterta pentru evenimentul blocked process report - una din modalitati este folosirea unei alerte WMI:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Blocked Process Report Alert',
        @message_id=0,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=4,
        @category_name=N'[Uncategorized]',
        @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
        @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

3. Selectati o modalitate de raspuns la alerta: trimitere email, folosire NET SEND etc.

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro