DBA Blocking Query Email Alert

Does anyone have a good Sql Alert which sends email notifications, when query Blocking has occurred over many minutes?
I know how to write my own, however seems to be a good code library on internet.
Please also give answer without event notification if possible.

I like this one below:
Email Notification when Blocking Occurs

I refrain from these as they utilize sys.processes which I hear is being deprecated:
Automatically Detect Blocking

Email Notification when Blocking Occurs

This is similar as first, however does not have as much detail:
Find Long Running Queries

Trying to locate good solution or consensus, before implementing. Feel free to send solution or edit one below.

Thanks,

Note: One below seems to have an unnecessary temp table step, which probably can be eliminated, with just one sql email.

```
*/
SET NOCOUNT ON

-- Checked for currently running queries by putting data in temp table
SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'WaitSec'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'ElapsSec'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY

I wrote Blocking Detector which uses event notification. You can find it here

Below is how it works :

This script will create 
    - a blocking detection alert
    - table to hold blocking information
    - modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec) 
    - create a sql agent job that will fire in resonse to the alert to capture blocking info

Full script :

```
/
Author: KIN SHAH
Date : 03/02/2011

Adapt the script as per your env --> places to change ---- CHANGE HERE !!

This script will create
- a blocking detection alert
- table to hold blocking information
- modify the 'blocked process threshold' sp_configure option to 5 mins (300 sec)
- create a sql agent job that will fire in resonse to the alert to capture blocking info

Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.

The following disclaimer applies to all code, scripts and demos available on my posts:

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree:

(i) to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded;
(ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and
(iii) to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

****/
USE [dbaalert] ---- CHANGE HERE !!
GO

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
drop table [dbo].[BlockingInfo];
-- change the blocking threshold to 5mins (300sec) *** ----
EXEC sp_configure 'blocked process threshold', 300 ---- CHANGE HERE !!
go
reconfigure with override
go
--------- ** create table to hold blocking data *** ----------

/ Object: Table [dbo].[BlockingInfo] Script Date: 02/17/2011 15:41:35 /
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].BlockingInfo NOT NULL,
[AlertTime] [datetime] NOT NULL,
[BlockingDetails] [xml] NULL,
[Notified] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RecordId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[BlockingInfo] ADD CONSTRAINT [DF_blocking_flag] DEFAULT ((0)) FOR [Notified]
GO
SET QUOTED_IDENTIFIER OFF;
GO
---------------------------- disable old and create new job---------------------------------------------
USE [msdb]
GO

--- disable old job on the server
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = N'DBA Group - Monitoring - Blocking Detector')
EXEC msdb.dbo.sp_update_job @job_name=N'DBA Group - Monitoring - Blocking Detector', @enabled = 0
GO

USE [msdb]
GO

/ Object: Job [DBA Group - Monitoring - Blocked Process Detector] Script Date: 03/02/2011 11:47:32 /
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/ Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/02/2011 11:47:33 /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA Group - Monitoring - Blocked Process Detector',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Authors: Kin Shah',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Insert Blocking info] Script Date: 03/02/2011 11:47:34 /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Blocking info',
@step_id=1,
@cmdexec_success_code=0,
@on_success_actio

StackExchange Database Administrators Q#205528, answer score: 5