Step 1: : Create below procedure in msdb database
use msdb
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Blocking_Notification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Blocking_Notification]
GO
/*
exec usp_Blocking_Notification 'venkateswara.sangu@gmail.com',500000
change DBMail profile & From address in below procedure before creating
*/
CREATE PROCEDURE usp_Blocking_Notification @Receipiant nvarchar(30),@waittime int = 100000
AS
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 sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID and
r.wait_time > @waittime
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) <> 0
BEGIN
-- blocking found, sent email.
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' +
N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' +
N'<th>WaitSec</th>' + N'<th>cpu_time</th>' +
N'<th>logical_reads</th>' + N'<th>reads</th>' +
N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' +
N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' +
N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' +
N'<th>open_transaction_count</th>' + '</tr>' + CAST((
SELECT td = s.session_id
,''
,td = r.STATUS
,''
,td = r.blocking_session_id
,''
,td = r.wait_type
,''
,td = wait_resource
,''
,td = r.wait_time / (1000.0)
,''
,td = r.cpu_time
,''
,td = r.logical_reads
,''
,td = r.reads
,''
,td = r.writes
,''
,td = r.total_elapsed_time / (1000.0)
,''
,td = 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)
,''
,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) +
N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')
,''
,td = r.command
,''
,td = s.login_name
,''
,td = s.host_name
,''
,td = s.program_name
,''
,td = s.host_process_id
,''
,td = s.last_request_end_time
,''
,td = s.login_time
,''
,td = r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
AND blocking_session_id > 0
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
FOR XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
,@from_address = 'DevDBAAdmin@techridesol.com'
,@body_format = 'HTML'
,@profile_name = N'TechRideSolDBMail'
,@recipients = @Receipiant
,@Subject = N'Blocking Detected'
END
DROP TABLE #temp_requests
Step 2 :
Connect to SQL Server using SSMS and Expand SQL Server Agent and right click on Jobs and select New Job
In New Job window provide the job name and owner as SA and select Steps Tab in left side pane
In Job Steps Window click on NEW button to create a job step
