Unanswered: How to get notification of long lock information?
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'Long_Lock'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Long_Lock'
EXECUTE msdb.dbo.sp_add_alert @name = N'Long_Lock', @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 60, @performance_condition = N'SQLServer:Locks|Lock Wait Time (ms)|Key|>|3600000', @include_event_description_in = 4, @category_name = N'[Uncategorized]'