Email notification on sqlserver database lock

We have a database lock situation happens on one production applications, it's caused by the application design, when one user make change and does not save it quickly, the other users will be blocked with no helpful information but hang screen.

I build this procedure to run every minute and send email alert to dba team and support staff, so we can talk to the blocker users to save the change or logoff for other users to continue their works.

This procedure only list the top 1 waiter and blocker, this is good enough for this application.

Here is the script.

CREATE  procedure [dbo].[alert_on_databaselock] as
declare @blockee_session_id varchar(100)
declare @blockee_wait_type varchar(100)
declare @wait_duration_ms varchar(100)
declare @blocker_session_id varchar(100)
declare @start_time varchar(100)
declare @status varchar(100)
declare @blockee_command varchar(100)
declare @database_id varchar(100)
declare @blockee_user_id varchar(100)
declare @blockee_host_name varchar(100)
declare @blockee_program_name varchar(100)
declare @blockee_login_name varchar(100)
declare @blockee_nt_user_name varchar(100)
declare @blockee_sql varchar(100)
declare @blocker_user_id varchar(100)
declare @blocker_host_name varchar(100)
declare @blocker_program_name varchar(100)
declare @blocker_login_name varchar(100)
declare @blocker_nt_user_name varchar(100)
declare @blocker_sql varchar(100)
declare @resource_description varchar(100)

SELECT top 1
@blockee_session_id=dm_ws.session_id ,
@blockee_wait_type = dm_ws.wait_type,
@wait_duration_ms=dm_ws.wait_duration_ms,
@blocker_session_id=dm_ws.blocking_session_id,
@resource_description=dm_ws.resource_description,
@start_time=dm_r.start_time,
@status=dm_r.status,
@blockee_command =dm_r.command,
@database_id=dm_r.database_id,
@blockee_user_id=dm_r.user_id ,
@blockee_host_name=dm_es.host_name ,
@blockee_program_name=dm_es.program_name,
@blockee_login_name=dm_es.login_name ,
@blockee_nt_user_name=dm_es.nt_user_name,
@blockee_sql=dm_t.text ,
@blocker_user_id=dm_r_blocker.user_id ,
@blocker_host_name=dm_es_blocker.host_name ,
@blocker_program_name=dm_es_blocker.program_name ,
@blocker_login_name=dm_es_blocker.login_name,
@blocker_nt_user_name=dm_es_blocker.nt_user_name,
@blocker_sql =dm_t_blocker.text
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_requests dm_r_blocker ON dm_ws.blocking_session_id = dm_r_blocker.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
left outer join sys.dm_exec_sessions dm_es_blocker ON dm_ws.blocking_session_id = dm_es_blocker.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_sql_text (dm_r_blocker.sql_handle) dm_t_blocker
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
and dm_ws.blocking_session_id is not null
order by dm_ws.wait_duration_ms desc

if @@rowcount=1
begin
declare @email_text varchar(1000)
declare @temptext varchar(1000)
set @email_text = 'Do not reply to this email, talk to DBA team about the database lock, ctvdba@ctv.ca'+char(10)+char(10)

set @temptext = '@blocker_host_name is : '+isnull(@blocker_host_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blockee_host_name is : '+isnull(@blockee_host_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@Wait start time is : '+isnull(@start_time,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@Wait duration is (miliseconds) : '+isnull(@wait_duration_ms,'N/A')+char(10)
set @email_text = @email_text+@temptext


set @temptext = '@Wait Type is : '+isnull(@blockee_wait_type,'N/A')+char(10)
set @email_text = @email_text+@temptext


set @temptext = '@Blocker session id is : '+isnull(@blocker_session_id,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@The blockee session id is : '+isnull(@blockee_session_id,'N/A')+char(10)
set @email_text=@email_text + @temptext


set @temptext = '@Resource dscription is : '+isnull(@resource_description,'N/A')+char(10)
set @email_text = @email_text+@temptext


set @temptext = '@Wait status is : '+isnull(@status,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@Blockee command is : '+isnull(@blockee_command,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@Database name is : '+isnull(db_name(@database_id),'N/A')+char(10)
set @email_text = @email_text+@temptext


set @temptext = '@blockee_program_name is : '+isnull(@blockee_program_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blockee_login_name is : '+isnull(@blockee_login_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blockee_nt_user_name is : '+isnull(@blockee_nt_user_name,'N/A')+char(10)
set @email_text = @email_text+@temptext


set @temptext = '@blocker_program_name is : '+isnull(@blocker_program_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blocker_login_name is : '+isnull(@blocker_login_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blocker_nt_user_name is : '+isnull(@blocker_nt_user_name,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = '@blocker_sql is : '+isnull(@blocker_sql,'N/A')+char(10)
set @email_text = @email_text+@temptext

set @temptext = char(10)+char(10)+'@blockee_sql is : '+isnull(@blockee_sql,'N/A')+char(10)
set @email_text = @email_text+@temptext

/*
print 'debug 4'
print @email_text
*/
EXEC msdb.dbo.sp_send_dbmail @recipients='myemail@gmail.com',
    @subject = 'db block notification',
    @body = @email_text,
    @body_format = 'text' ;
end;
GO

exec alert_on_databaselock