Email Sqlserver Block monitor script

Here is the Script which monitors and triggers mail to DBA with SPID and Code which got blocked by other  session id’s in any of the sqlserver databases.

This script gives in depth information to troubleshoot the blocking sessions.

/**—Create an Sqlserver Agent Job to report Blocking events and Mail to DBA team—-**/

declare @subj varchar(max),
@cmd varchar(max),
@file varchar(100)
select    @subj = @@servername  + ‘—Encountered Blocking sessions’,
@cmd = ‘Print ”Below listed Session_Id are blocked by.
————————————–

”
select r.Session_id as SID,
r.Start_time,
DBName = db_name(database_id),
r.Command,
substring(st.text,
r.statement_start_offset/2+1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), st.text)) * 2 else r.statement_end_offset end – r.statement_start_offset)/2+1) As BlockedQuery,
r.Blocking_session_id,
s.Memory_usage,
s.Host_name,
s.Program_name,
s.Login_name
from sys.dm_exec_requests r
join sys.dm_exec_connections c on r.session_id = c.session_id
join sys.dm_exec_sessions s on s.session_id = r.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where r.session_id > 50 and r.blocking_session_id>1
order by r.session_id
‘

exec msdb.dbo.sp_send_dbmail
@profile_name = ‘sql – dba’,
@recipients = ‘shamsul.hassan@hassanszone.com’,
@subject = @subj,
@body= ”,
@query = @cmd,
@query_result_separator = ‘|’,
@query_result_no_padding = 1

/**—Create an Sqlserver Alert to trigger Blocking alerts Mail to DBA team—-**/

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N’Request Blocked Processes’,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N”,
@notification_message=N”,
@event_description_keyword=N”,
@performance_condition=N’SQLSERVER:General Statistics|Processes blocked||>|1′,
@wmi_namespace=N”,
@wmi_query=N”,
—        @job_id=N’bc259ae4-d96b-4146-a372-1343e91cb5de’,
@job_name=’DBA_BlockingCheck_Report’
GO