Thanks in advance for your help.
I am working on a blocking issue of a production db. I try to implement something so SQL Server can send me an alert if there is an application locks a resource for more than 1 hour. I use sp_add_alert procedure to create an alert and this alert suppose to send an alert to me if SQLServer:Locks Lock Wait Time > 3600 seconds. But it is not working, please advise.
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Block'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Block'
EXECUTE msdb.dbo.sp_add_alert @name = N'Block', @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 60, @performance_condition = N'SQLServer:Locks|Lock Wait Time (ms)|Key|>|3600000', @include_event_description_in = 1, @category_name = N'[Uncategorized]'
EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Block', @operator_name = N'MyDBA', @notification_method = 1