Script to share lead blocker details to mail and store lead blocker details in physical table.

The below script will help to send both blocking and blocked sessions along with query in 

single mail .

It will take automatically default mail profiler if it configured and send you mail

for the required list of recipients.

DECLARE @waittime AS INT = 180000
DECLARE @count VARCHAR(100)
DECLARE @dbname VARCHAR(100)
DECLARE @vcsubject VARCHAR(1000)

SELECT @count = count(*)
FROM master..sysprocesses
WHERE (blocked != 0)
 AND spid != blocked
 AND cmd != 'DB MIRROR'
 AND waittime > @waittime

IF (@count) >= 1
BEGIN
 DECLARE @tableHTML NVARCHAR(MAX);
 DECLARE @Servername VARCHAR(100);

 SELECT @Servername = convert(VARCHAR(100), SERVERPROPERTY('servername'));

 DECLARE @MAILPROFILE VARCHAR(100);

 IF OBJECT_ID('tempdb..#MAILDEFAULPROFILE') IS NOT NULL
  DROP TABLE #MAILDEFAULPROFILE

 CREATE TABLE #MAILDEFAULPROFILE (
  PRINCIPALID INT
  ,PRINCIPALNAME VARCHAR(200)
  ,PROFILEID INT
  ,PROFILENAME VARCHAR(200)
  ,ISDEFAULT BIT
  );

 INSERT INTO #MAILDEFAULPROFILE
 EXEC MSDB.DBO.SYSMAIL_HELP_PRINCIPALPROFILE_SP;

 SELECT @MAILPROFILE = PROFILENAME
 FROM #MAILDEFAULPROFILE
 WHERE ISDEFAULT = 1;

 SELECT *
 FROM #MAILDEFAULPROFILE

 IF OBJECT_ID('tempdb..##temptab') IS NOT NULL
  DROP TABLE ##temptab

 IF OBJECT_ID('tempdb..##Emailoutput') IS NOT NULL
  DROP TABLE ##Emailoutput

 CREATE TABLE ##temptab (blk INT)

 INSERT INTO ##temptab (blk)
 SELECT blocked
 FROM sys.sysprocesses
 WHERE blocked NOT IN (
   SELECT spid
   FROM sys.sysprocesses
   WHERE blocked <> 0
   )
  AND blocked <> 0

 CREATE TABLE ##Emailoutput (
  [Root Blocking SPID] VARCHAR(50)
  ,[Blocking Host] VARCHAR(100)
  ,[Blocking Login] VARCHAR(100)
  ,ProgramName VARCHAR(1000)
  ,[Blocked SPID] VARCHAR(50)
  ,[Blocking SPID] VARCHAR(50)
  ,[Blocked Status] VARCHAR(50)
  ,[Blocked Login] VARCHAR(100)
  ,[Blocked Host] VARCHAR(100)
  ,[Wait Time in Mins] VARCHAR(50)
  ,[blocked text] VARCHAR(max)
  ,[Root Blocking SPID Text] VARCHAR(max)
  )

 DECLARE @blc INT

 DECLARE cur CURSOR
 FOR
 SELECT DISTINCT blk
 FROM ##temptab

 OPEN cur

 FETCH NEXT
 FROM cur
 INTO @blc

 WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO ##Emailoutput
  SELECT x.SESsION_ID AS [Root Blocking SPID]
   ,x.host_name [Blocking Host]
   ,x.login_name [Blocking Login]
   ,ProgramName
   ,y.*
   ,cast(x.SESsION_ID AS VARCHAR(10)) + '  - ' + x.TEXT [Root Blocking SPID Text]
  FROM (
   SELECT es.*
    ,program_name ProgramName
    ,st.TEXT
   FROM sys.dm_exec_sessions es
   INNER JOIN sys.dm_exec_connections ec ON ec.session_id = es.session_id
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
   WHERE es.session_id = @blc
   ) x
   ,(
    SELECT spid [Blocked SPID]
     ,blocked [Blocking SPID]
     ,STATUS [Blocked Status]
     ,loginame [Blocked Login]
     ,hostname [Blocked Host]
     ,waittime / 60000 [Wait Time in Mins]
     ,cast(spid AS VARCHAR(10)) + ' - ' + TEXT [blocked text]
    FROM sys.sysprocesses r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    WHERE r.blocked = @blc
    ) y

  FETCH NEXT
  FROM cur
  INTO @blc
 END

 CLOSE cur

 DEALLOCATE cur

 INSERT INTO Test..LeadBlockersHistory
 SELECT *
 FROM ##Emailoutput

 SET @tableHTML = N'<H1>Blocking on  ' + N' Servername : '

 + @Servername + '</H1>' + N'<table border="1">' 

+ N'<tr><th>Root Blocking SPID</th><th>Blocking Host</th>

<th>Blocking Login</th><th>ProgramName</th>' 

+ N'<th>Blocked SPID</th><th>Blocking SPID</th>

<th>Blocked Status</th><th>Blocked Login</th><th>Blocked Host</th>'

 + N'<th>Wait Time in Mins</th><th>Blocked Text</th>

<th>Root Blocking SPID Text</th>' + CAST((
    SELECT td = CAST([Root Blocking SPID] AS VARCHAR(50))
     ,''
     ,td = CAST([Blocking Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking Login] AS VARCHAR(100))
     ,''
     ,td = CAST(ProgramName AS VARCHAR(1000))
     ,''
     ,td = CAST([Blocked SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Status] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Login] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Wait Time in Mins] AS VARCHAR(100))
     ,''
     ,td = CAST([blocked text] AS VARCHAR(max))
     ,''
     ,td = CAST([Root Blocking SPID Text] AS VARCHAR(max))
     ,''
    FROM ##Emailoutput
    FOR XML PATH('tr')
     ,TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';
 SET @vcsubject = 'Blocking occuring in ' 

+ convert(VARCHAR(100), SERVERPROPERTY('servername')) + ', Please start monitoring'

 EXEC msdb.dbo.sp_send_dbmail @recipients = 'XYZ@MAIL.com'
  ,@profile_name = @MAILPROFILE
  ,@subject = @vcsubject
  ,@body = @tableHTML
  ,@body_format = 'HTML';
END