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'
GO3. 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