Blocking Alerts

Set NOCOUNT ON
if((select COUNT(spid) from sys.sysprocesses where blocked<>0)>0)
begin

DECLARE @tableHTML  NVARCHAR(MAX) ; 

SET @tableHTML = 
    N'<H1>Blocking Information</H1>' + 
    CAST ( ( SELECT spid,       ' is blocked by  ', 
                    blocked, ' on Database ', 
                    DB_NAME(dbid), ' from application server ', 
                    hostname, ' through application name ', 
                    program_name, ' and login is ', 
                    loginame 
              from master.sys.sysprocesses where blocked >0
              FOR XML PATH('tr'), TYPE
              )
              AS NVARCHAR(MAX) ) + 
    N'</table>' ;
    --select @tableHTML

exec msdb..sp_send_dbmail @profile_name= 'DBA' 
    ,  @recipients= 'xxx@xxx.com' 
    
    ,  @subject= 'Blocking Alerts '  
    ,  @body= @tableHTML,
    @body_format = 'HTML' ;
end