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

Question

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

Answer 1

The msdb.dbo.sp_update_alert system stored procedure updates records in the msdb.dbo.sysalerts table. The nvarchar(512) parameter, "@notification_message" gets stored in the msdb.dbo.sysalerts.notification_message column. When an alert is triggered, the contents of that column are pulled for the message. I have not tried this before, but one thing you could try is to create a SQL Agent job that modifies the value in msdb.dbo.sysalerts.notification_message and attach that job to the notification by using either the @job_id or @job_name parameters. If you're lucky, the job will be executed before the notification is sent out, thus "dynamically" changing the text of the notification. What I expect is more likely is that the job will be run at the same time and would only affect the next time that this alert is triggered. But depending on what you're looking to see, this might be good enough.

For more information, go into your MSDB database and run sp_helptext sp_update_alert and you can see what it's doing.

One other option is to have your SQL Agent job send a message using sp_send_dbmail. Then you can customize your message all you want.