Microsoft SQL Server Management Studio - Alerts with additional information of lock

Vote count:

1

We want to have an alert when a lock is waiting longer than 60 seconds. The alert script below is performing as expected.

But we'd like to have more information like the locked Session ID, Locking Status, login name, etc.

Is there a way to include this in the @notification_message?

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Total Lock Wait Time (ms) > 60000', 
        @message_id=0, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @database_name=N'', 
        @notification_message=N'', 
        @event_description_keyword=N'', 
        @performance_condition=N'MSSQL$DB:Locks|Lock Wait Time (ms)|_Total|>|60000', 
        @wmi_namespace=N'', 
        @wmi_query=N'', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Total Lock Wait Time (ms) > 60000', @operator_name=N'me', @notification_method = 1
GO

edited 24 mins ago

utility

774

asked 1 hour ago

Rogier

400

Microsoft SQL Server Management Studio - Alerts with additional information of lock